locked
How to do... using Pivot/Unpivot in MSSQL RRS feed

  • Question

  • User-582711651 posted

    Dear Experts,

    There is a Query requirement for my project, kindly help me

    Ref my script/table details

    Declare @FromDate Date
    Select @FromDate = '2020/07/01' 
    
    IF OBJECT_ID(N'tempdb..#TmpTbl') IS NOT NULL
    BEGIN
    DROP TABLE #TmpTbl
    END
    
    Create Table #TmpTbl(FunderID INT,FunderName varchar(100),RS_Date Date,RS_PRS Numeric(14,0),RS_INT Numeric(14,0))
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/05',300,100)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/06',100,60)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/13',750,150)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (2,'NP UTR ','2020/08/06',600,300)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (3,'SIP KCM ','2020/08/01',500,750)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (3,'SIP KCM ','2020/12/01',1500,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (2,'NP UTR ','2021/01/12',900,400)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/20',900,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2022/01/20',500,100)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2020/10/31',100,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2022/06/15',600,450)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2022/12/20',100,50)
    
    Select * from #TmpTbl where RS_Date >= @FromDate Order by FunderID,RS_Date
    

    the following result is expected. 

    For RS_PRI

    RS_PRI - Month CNA & CO NP UTR  SIP KCM  KG MS Total
    01 - 07 JUL 2020 400       400
    08 - 14 JUL 2020 750       750
    15 - 31 JUL 2020 900       900
    AUG 2020   600 500   1100
    SEP 2020         0
    OCT 2020       100 100
    NOV 2020         0
    DEC 2020     1500   1500
    JAN 2021   900     900
    FEB 2021         0
    MAR 2021         0
    APR 2021         0
    MAY 2021         0
    JUN 2021         0
    JUL 2021         0
    AUG 2021         0
    SEP 2021         0
    OCT 2021         0
    NOV 2021         0
    DEC 2021         0
    JAN 2022 500       500
    FEB 2022         0
    MAR 2022         0
    APR 2022         0
    MAY 2022         0
    JUN 2022       600 600
    JUL 2022         0
    AUG 2022         0
    SEP 2022         0
    OCT 2022         0
    NOV 2022         0
    DEC 2022       100 100
    Gr.Total 2550 1500 2000 800 6850

    Note: Based on the where RS_Date >= @FromDate) report will be generated. 

    Similar to this I will do for RS_INT.

    Thanks in advance. 

    Sunday, August 30, 2020 6:20 AM

Answers

  • User-1330468790 posted

    Hi Ayyappan,

     

    Since the logic behind the result table is complicated, I think you might need to use "UNION ALL" to integrate different tables.

    • Table for the first 7 days of the input month
    • Table for the second 7 days of the input month
    • Table for the rest days of the input month  
    • Table for the following months
    • Table for the grand total

    You could refer to below SQL query based on your example data.

    Declare @FromDate Date,
    @ToDate Date,
    @StartDate1 Date,
    @StartDate2 Date,
    @StartDate3 Date,
    @EndDate1 Date,
    @EndDate2 Date,
    @EndDate3 Date
    
    DROP TABLE IF Exists #TmpTbl
    
    
    Create Table #TmpTbl(FunderID INT,FunderName varchar(100),RS_Date Date,RS_PRS Numeric(14,0),RS_INT Numeric(14,0))
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/05',300,100)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/06',100,60)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/13',750,150)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (2,'NP UTR ','2020/08/06',600,300)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (3,'SIP KCM ','2020/08/01',500,750)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (3,'SIP KCM ','2020/12/01',1500,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (2,'NP UTR ','2021/01/12',900,400)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2020/07/20',900,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (1,'CNA & CO','2022/01/20',500,100)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2020/10/31',100,50)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2022/06/15',600,450)
    Insert into #TmpTbl(FunderID,FunderName,RS_Date,RS_PRS,RS_INT)
    Values			    (4,'KG MS','2022/12/20',100,50)
    
    
    
    
    Select @FromDate = '2020/07/01' 
    -- Max date in the table
    SELECT @ToDate = MAX(RS_Date) FROM #TmpTbl
    --First day of the input month
    SELECT @StartDate1 = DATEADD(DAY,1,EOMONTH(@FromDate,-1))
    -- The 7th day of the input month
    SELECT @EndDate1 = DATEADD(DAY,6, @StartDate1)
    -- The 8th day of the input month
    SELECT @StartDate2 = DATEADD(DAY,1,@EndDate1)
    -- The 14th day of the input month
    SELECT @EndDate2 = DATEADD(DAY,6, @StartDate2)
    -- The 15th day of the input month
    SELECT @StartDate3 = DATEADD(DAY,1,@EndDate2)
    -- The Last day of the input month
    SELECT @EndDate3 = EOMONTH(@FromDate)
    
    
    -- Get all months after the input month
    ;WITH mcte AS
    (
            SELECT DATEADD(MONTH, 1, @FromDate) AS MonthYear
            UNION ALL
            SELECT DATEADD(MONTH, 1, MonthYear)
            FROM mcte
            WHERE EOMONTH(MonthYear) < @ToDate
    )
    
    
    -- From 01 to 07
    SELECT CAST(DAY(@StartDate1) AS varchar) + '-' + CAST(DAY(@EndDate1) AS varchar) + ' ' + Format(@FromDate,'MMM') + ' ' + CAST(year(@FromDate) as varchar) [RS_PRI - Month], 
    SUM(CASE WHEN FunderName = 'CNA & CO' THEN [RS_PRS] ELSE 0 END) [CNA & CO],
    SUM(CASE WHEN FunderName = 'NP UTR' THEN [RS_PRS] ELSE 0 END) [NP UTR],
    SUM(CASE WHEN FunderName = 'SIP KCM' THEN [RS_PRS] ELSE 0 END) [SIP KCM],
    SUM(CASE WHEN FunderName = 'KG MS' THEN [RS_PRS] ELSE 0 END) [KG MS],
    SUM([RS_PRS]) Total
    FROM #TmpTbl
    WHERE RS_Date BETWEEN @StartDate1 AND @EndDate1
    
    UNION ALL
    
    -- From 08 to 14
    SELECT CAST(DAY(@StartDate2) AS varchar) + '-' + CAST(DAY(@EndDate2) AS varchar) + ' ' + Format(@FromDate,'MMM') + ' ' + CAST(year(@FromDate) as varchar) [RS_PRI - Month], 
    SUM(CASE WHEN FunderName = 'CNA & CO' THEN [RS_PRS] ELSE 0 END) [CNA & CO],
    SUM(CASE WHEN FunderName = 'NP UTR' THEN [RS_PRS] ELSE 0 END) [NP UTR],
    SUM(CASE WHEN FunderName = 'SIP KCM' THEN [RS_PRS] ELSE 0 END) [SIP KCM],
    SUM(CASE WHEN FunderName = 'KG MS' THEN [RS_PRS] ELSE 0 END) [KG MS],
    SUM([RS_PRS]) Total
    FROM #TmpTbl
    WHERE RS_Date BETWEEN @StartDate2 AND @EndDate2
    
    UNION ALL
    
    -- From 15 to End of the month
    SELECT CAST(DAY(@StartDate3) AS varchar) + '-' + CAST(DAY(@EndDate3) AS varchar) + ' ' + Format(@FromDate,'MMM') + ' ' + CAST(year(@FromDate) as varchar) [RS_PRI - Month], 
    SUM(CASE WHEN FunderName = 'CNA & CO' THEN [RS_PRS] ELSE 0 END) [CNA & CO],
    SUM(CASE WHEN FunderName = 'NP UTR' THEN [RS_PRS] ELSE 0 END) [NP UTR],
    SUM(CASE WHEN FunderName = 'SIP KCM' THEN [RS_PRS] ELSE 0 END) [SIP KCM],
    SUM(CASE WHEN FunderName = 'KG MS' THEN [RS_PRS] ELSE 0 END) [KG MS],
    SUM([RS_PRS]) Total
    FROM #TmpTbl
    WHERE RS_Date BETWEEN @StartDate3 AND @EndDate3
    
    UNION ALL
    
    -- For the following months
    SELECT Format(MonthYear,'MMM') + ' ' + CAST(year(MonthYear) as varchar) AS [RS_PRI - Month], 
    SUM(CASE WHEN FunderName = 'CNA & CO' THEN ISNULL([RS_PRS],0) ELSE 0 END) [CNA & CO],
    SUM(CASE WHEN FunderName = 'NP UTR' THEN ISNULL([RS_PRS],0) ELSE 0 END) [NP UTR],
    SUM(CASE WHEN FunderName = 'SIP KCM' THEN ISNULL([RS_PRS],0) ELSE 0 END) [SIP KCM],
    SUM(CASE WHEN FunderName = 'KG MS' THEN ISNULL([RS_PRS],0) ELSE 0 END) [KG MS],
    SUM(ISNULL([RS_PRS],0)) Total
    FROM mcte a 
    LEFT JOIN #TmpTbl b ON MONTH(a.MonthYear) = MONTH(b.RS_Date) AND YEAR(a.MonthYear) = YEAR(b.RS_Date)
    GROUP BY MonthYear
    
    UNION ALL
    -- For grand total
    SELECT 'Gr.Total' AS [RS_PRI - Month], 
    SUM(CASE WHEN FunderName = 'CNA & CO' THEN ISNULL([RS_PRS],0) ELSE 0 END) [CNA & CO],
    SUM(CASE WHEN FunderName = 'NP UTR' THEN ISNULL([RS_PRS],0) ELSE 0 END) [NP UTR],
    SUM(CASE WHEN FunderName = 'SIP KCM' THEN ISNULL([RS_PRS],0) ELSE 0 END) [SIP KCM],
    SUM(CASE WHEN FunderName = 'KG MS' THEN ISNULL([RS_PRS],0) ELSE 0 END) [KG MS],
    SUM(ISNULL([RS_PRS],0)) Total
    FROM #TmpTbl
    
    

    Result:

     

    The logic for each part is similar that you could construct a function to make the codes more clear.

     

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 31, 2020 9:40 AM