locked
Pivot Unpivot Data - Display value dates data in parallel rows RRS feed

  • Question

  • Hi Friends,

    I am seeking your valuable inputs with regard to one task which I have in hand. I have data in below format:

    Ultimately want to display them in this format

    Table Script

    Use tempdb;
      
    CREATE TABLE Payments
    (
    value_date DATE,
    sourcesystemid INT,
    costcentreid INT,
    accountid INT,
    YTDBalance numeric(34,18),
    YTDBalanceLocalCCY numeric(34,18)
    )
    
    Insert into Payments Values
    ('2017/07/26',	1,	1,	10088,	10,	18),
    ('2017/07/26',	1,	1,	10054,	20,	28),
    ('2017/07/26',	1,	12,	10003,	30,	48),
    ('2017/07/26',	1,	10,	10007,	40,	57),
    ('2017/07/27',	1,	10,	10054,	50,	68),
    ('2017/07/27',	1,	12,	10003,	60,	78),
    ('2017/07/27',	1,	12,	10088,	70,	88),
    ('2017/07/27',	1,	9,	10006,	80,	98),
    ('2017/07/27',	1,	1,	10009,	10,	18),
    ('2017/07/29',	1,	18,	10010,	20,	38),
    ('2017/07/29',	1,	10,	10054,	30,	48),
    ('2017/07/29',	1,	12,	10003,	40,	58),
    ('2017/07/29',	1,	1,	10088,	50,	68)

    I am trying to use PIVOT but seems not able to use them rightly. Any help/pointers will be greatly appreciated.

    Correction:

    We will be passing 2 dates example say: 2017-07-26 and 2017-07-29 so result should show us data for those 2 dates only (not 3 as in above screenshot)


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


    • Edited by GURSETHI Thursday, September 14, 2017 10:18 PM addition
    Thursday, September 14, 2017 9:58 PM

Answers

  • Hi GURSETHI,

    Please try this.

    CREATE TABLE Payments
    (
    value_date DATE,
    sourcesystemid INT,
    costcentreid INT,
    accountid INT,
    YTDBalance numeric(34,18),
    YTDBalanceLocalCCY numeric(34,18)
    )
    
    Insert into Payments Values
    ('2017/07/26',	1,	1,	10088,	10,	18),
    ('2017/07/26',	1,	1,	10054,	20,	28),
    ('2017/07/26',	1,	12,	10003,	30,	48),
    ('2017/07/26',	1,	10,	10007,	40,	57),
    ('2017/07/27',	1,	10,	10054,	50,	68),
    ('2017/07/27',	1,	12,	10003,	60,	78),
    ('2017/07/27',	1,	12,	10088,	70,	88),
    ('2017/07/27',	1,	9,	10006,	80,	98),
    ('2017/07/27',	1,	1,	10009,	10,	18),
    ('2017/07/29',	1,	18,	10010,	20,	38),
    ('2017/07/29',	1,	10,	10054,	30,	48),
    ('2017/07/29',	1,	12,	10003,	40,	58),
    ('2017/07/29',	1,	1,	10088,	50,	68)
    
    declare @v_value_date1 date
    declare @v_value_date2 date
    declare @v_columns varchar(500)
    declare @v_sql varchar(max)
    
    set @v_value_date1='2017-07-26'
    set @v_value_date2='2017-07-29'
    
    ;with pivot_data as
    (
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+' '+'YTDBalance' as balance_type,YTDBalance as Balance,value_date from Payments
    union all
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+' '+'YTDBalanceLocalCCY' as balance_type,YTDBalanceLocalCCY as Balance,value_date from Payments
    )
    select @v_columns=STUFF((select distinct ','+QUOTENAME(balance_type) from pivot_data
    where value_date in (@v_value_date1,@v_value_date2)
    for xml path('')),1,1,'')
    --PRINT @v_columns
    
    set @v_sql='
    ;with pivot_data as
    (
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+'' ''+''YTDBalance'' as balance_type,YTDBalance as Balance,value_date from Payments
    union all
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+'' ''+''YTDBalanceLocalCCY'' as balance_type,YTDBalanceLocalCCY as Balance,value_date from Payments
    )
    select sourcesystemid,costcentreid,accountid,'+@v_columns+'
    from (
    select sourcesystemid,costcentreid,accountid,balance_type,Balance from pivot_data 
    where value_date in ('''+convert(varchar(10),@v_value_date1,110)+''','''+convert(varchar(10),@v_value_date2,110)+''')) AS src pivot
    ( 
     max(Balance) for balance_type in ('+@v_columns+')) AS PVT'
     --PRINT @v_sql
    Exec(@v_sql)
    
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GURSETHI Friday, September 15, 2017 11:55 PM
    Friday, September 15, 2017 3:26 AM
  • -- can you try this DECLARE @fromDate AS DATETIME; DECLARE @toDate AS DATETIME; -- Change the Parameter values here SET @fromDate ='2017-Jul-26'; SET @toDate ='2017-Jul-27' ; -- CODE FROM HERE BEGIN TRY DROP TABLE ##TEMP END TRY BEGIN CATCH END CATCH ;

    SELECT SourceSystemID, CostCentreID, AccountID , YTDBalance , Value_Date ,CONVERT( VARCHAR, CAST(Value_Date AS DATETIME) , 112 ) +'_YTDBalance' ColName INTO ##Temp FROM payments WHERE Value_Date IN( @fromDate , @toDate) /*WHERE Value_Date BETWEEN @fromDate AND @toDate /* IF You want all Days BETWEEN @fromDate AND @toDate */ */ UNION ALL SELECT SourceSystemID, CostCentreID, AccountID , YTDBalanceLocalCCY , Value_Date ,CONVERT( VARCHAR, CAST(Value_Date AS DATETIME) , 112 ) +'_YTDBalanceLocalCCY' ColName FROM payments WHERE Value_Date IN( @fromDate , @toDate) ; /*WHERE Value_Date BETWEEN @fromDate AND @toDate /* IF You want all Days BETWEEN @fromDate AND @toDate */ */ DECLARE @Pvt_Col_List AS VARCHAR(max) ; SET @Pvt_Col_List ='' ; SELECT @Pvt_Col_List = @Pvt_Col_List +','+ QUOTENAME(i.ColName ) From ##Temp i GROUP BY ColName;; SELECT @Pvt_Col_List = SUBSTRING(@Pvt_Col_List, 2, 7000) ; PRINT @Pvt_Col_List ; DECLARE @SQL_YTDBalance AS VARCHAR(7999); SET @SQL_YTDBalance = ' SELECT pvt.SourceSystemID, pvt.CostCentreID, pvt.AccountID , ' + @Pvt_Col_List +' FROM ( SELECT SourceSystemID, CostCentreID, AccountID, YTDBalance ,ColName FROM ##Temp ) AS tmp PIVOT ( Sum([YTDBalance]) for ColName IN (' + @Pvt_Col_List +') ) AS pvt;'; PRINT @SQL_YTDBalance ; EXEC ( @SQL_YTDBalance) ;



    • Edited by msbi_Dev Friday, September 15, 2017 9:02 PM
    • Marked as answer by GURSETHI Friday, September 15, 2017 11:55 PM
    Friday, September 15, 2017 9:01 PM

All replies

  • Hi GURSETHI,

    Please try this.

    CREATE TABLE Payments
    (
    value_date DATE,
    sourcesystemid INT,
    costcentreid INT,
    accountid INT,
    YTDBalance numeric(34,18),
    YTDBalanceLocalCCY numeric(34,18)
    )
    
    Insert into Payments Values
    ('2017/07/26',	1,	1,	10088,	10,	18),
    ('2017/07/26',	1,	1,	10054,	20,	28),
    ('2017/07/26',	1,	12,	10003,	30,	48),
    ('2017/07/26',	1,	10,	10007,	40,	57),
    ('2017/07/27',	1,	10,	10054,	50,	68),
    ('2017/07/27',	1,	12,	10003,	60,	78),
    ('2017/07/27',	1,	12,	10088,	70,	88),
    ('2017/07/27',	1,	9,	10006,	80,	98),
    ('2017/07/27',	1,	1,	10009,	10,	18),
    ('2017/07/29',	1,	18,	10010,	20,	38),
    ('2017/07/29',	1,	10,	10054,	30,	48),
    ('2017/07/29',	1,	12,	10003,	40,	58),
    ('2017/07/29',	1,	1,	10088,	50,	68)
    
    declare @v_value_date1 date
    declare @v_value_date2 date
    declare @v_columns varchar(500)
    declare @v_sql varchar(max)
    
    set @v_value_date1='2017-07-26'
    set @v_value_date2='2017-07-29'
    
    ;with pivot_data as
    (
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+' '+'YTDBalance' as balance_type,YTDBalance as Balance,value_date from Payments
    union all
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+' '+'YTDBalanceLocalCCY' as balance_type,YTDBalanceLocalCCY as Balance,value_date from Payments
    )
    select @v_columns=STUFF((select distinct ','+QUOTENAME(balance_type) from pivot_data
    where value_date in (@v_value_date1,@v_value_date2)
    for xml path('')),1,1,'')
    --PRINT @v_columns
    
    set @v_sql='
    ;with pivot_data as
    (
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+'' ''+''YTDBalance'' as balance_type,YTDBalance as Balance,value_date from Payments
    union all
    select sourcesystemid,costcentreid,accountid,convert(varchar(10),value_date,103)+'' ''+''YTDBalanceLocalCCY'' as balance_type,YTDBalanceLocalCCY as Balance,value_date from Payments
    )
    select sourcesystemid,costcentreid,accountid,'+@v_columns+'
    from (
    select sourcesystemid,costcentreid,accountid,balance_type,Balance from pivot_data 
    where value_date in ('''+convert(varchar(10),@v_value_date1,110)+''','''+convert(varchar(10),@v_value_date2,110)+''')) AS src pivot
    ( 
     max(Balance) for balance_type in ('+@v_columns+')) AS PVT'
     --PRINT @v_sql
    Exec(@v_sql)
    
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GURSETHI Friday, September 15, 2017 11:55 PM
    Friday, September 15, 2017 3:26 AM
  • -- can you try this DECLARE @fromDate AS DATETIME; DECLARE @toDate AS DATETIME; -- Change the Parameter values here SET @fromDate ='2017-Jul-26'; SET @toDate ='2017-Jul-27' ; -- CODE FROM HERE BEGIN TRY DROP TABLE ##TEMP END TRY BEGIN CATCH END CATCH ;

    SELECT SourceSystemID, CostCentreID, AccountID , YTDBalance , Value_Date ,CONVERT( VARCHAR, CAST(Value_Date AS DATETIME) , 112 ) +'_YTDBalance' ColName INTO ##Temp FROM payments WHERE Value_Date IN( @fromDate , @toDate) /*WHERE Value_Date BETWEEN @fromDate AND @toDate /* IF You want all Days BETWEEN @fromDate AND @toDate */ */ UNION ALL SELECT SourceSystemID, CostCentreID, AccountID , YTDBalanceLocalCCY , Value_Date ,CONVERT( VARCHAR, CAST(Value_Date AS DATETIME) , 112 ) +'_YTDBalanceLocalCCY' ColName FROM payments WHERE Value_Date IN( @fromDate , @toDate) ; /*WHERE Value_Date BETWEEN @fromDate AND @toDate /* IF You want all Days BETWEEN @fromDate AND @toDate */ */ DECLARE @Pvt_Col_List AS VARCHAR(max) ; SET @Pvt_Col_List ='' ; SELECT @Pvt_Col_List = @Pvt_Col_List +','+ QUOTENAME(i.ColName ) From ##Temp i GROUP BY ColName;; SELECT @Pvt_Col_List = SUBSTRING(@Pvt_Col_List, 2, 7000) ; PRINT @Pvt_Col_List ; DECLARE @SQL_YTDBalance AS VARCHAR(7999); SET @SQL_YTDBalance = ' SELECT pvt.SourceSystemID, pvt.CostCentreID, pvt.AccountID , ' + @Pvt_Col_List +' FROM ( SELECT SourceSystemID, CostCentreID, AccountID, YTDBalance ,ColName FROM ##Temp ) AS tmp PIVOT ( Sum([YTDBalance]) for ColName IN (' + @Pvt_Col_List +') ) AS pvt;'; PRINT @SQL_YTDBalance ; EXEC ( @SQL_YTDBalance) ;



    • Edited by msbi_Dev Friday, September 15, 2017 9:02 PM
    • Marked as answer by GURSETHI Friday, September 15, 2017 11:55 PM
    Friday, September 15, 2017 9:01 PM
  • Thanks Will & Bala both solutions worked and resolved my problem. Thanks for your help. Both examples gave a new dimension to my learning on PIVOTS.

    Thanks again


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Friday, September 15, 2017 11:55 PM