locked
SQL Pivot Dynamic RRS feed

  • Question

  • Hi,

    I'm looking to pivot the below data based on dates... can some please point out the mistake or a reference link.

    Date columns are dynamic.

    My Data looks like 

    ActionStatus	Cnt	Totals	ImportedDate	%Percent %
    Completed	6	214	27/10/2016	2
    Required	14	214	27/10/2016	6
    Action	        15	214	27/10/2016	7
    Action Required	146	214	27/10/2016	68
    Change	        10	214	27/10/2016	4
    ManRequired	23	214	27/10/2016	10
    Completed	6	59	28/10/2016	10
    Required	4	59	28/10/2016	6
    Action	        3	59	28/10/2016	5
    Action Required	8	59	28/10/2016	13
    Change	        6	59	28/10/2016	10
    ManRequired	32	59	28/10/2016	54
    Action	        2	3	29/10/2016	66
    Change	        1	3	29/10/2016	33
    Action Required	6	8	30/10/2016	75
    ManRequired	2	8	30/10/2016	25
    Required	6	45	31/10/2016	13
    Action	        2	45	31/10/2016	4
    Action Required	31	45	31/10/2016	68
    Change	        1	45	31/10/2016	2
    ManRequired	5	45	31/10/2016	11

    My SQL Code is derived from this Ref link  

    But I'm looking for Grand total at the bottom with a % under date column

    My SQL Code

    create table #Temp_Dataset (ActionStatus varchar(20), ImportedDate date)
    insert into #Temp_Dataset
    values('Completed', '2016/10/27'),
    ('Required','2016/10/27'),
    ('Action','2016/10/27'),
    ('Action','2016/10/27')
    ,('Action Required','2016/10/27')
    ,('Change','2016/10/27')
    ,('ManRequired','2016/10/27')
    ,('Completed','2016/10/28')
    ,('Required','2016/10/28')
    ,('Action','2016/10/28')
    ,('Action Required','2016/10/28')
    ,('Change','2016/10/28')
    ,('ManRequired','2016/10/28')
    ,('Action','2016/10/29')
    ,('Change','2016/10/29')
    ,('Action Required','2016/10/30')
    ,('ManRequired','2016/10/30')
    ,('Required','2016/10/31')
    ,('Action','2016/10/31')
    ,('Action Required','2016/10/31')
    ,('Change','2016/10/31')
    ,('ManRequired','2016/10/31')
    
    
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    SELECT @cols = STUFF((SELECT ',' + QUOTENAME(ImportedDate) 
                        from #Temp_Dataset
                        GROUP BY ImportedDate                   
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT ActionStatus,' + @cols + ' FROM 
                 (
                    SELECT [ActionStatus], ImportedDate, COUNT(ImportedDate) AS cntTotal
    			 FROM #Temp_Dataset
    			 GROUP BY ActionStatus, ImportedDate
                ) x
                pivot 
                (
                    SUM(cntTotal)
                    FOR ImportedDate in (' + @cols + ')
                ) p 
    		  '
    
    EXECUTE(@query);
    
    drop table #Temp_Dataset

    Desired output:

    • Edited by StSingh Tuesday, November 1, 2016 10:19 PM as per the comments below
    Tuesday, November 1, 2016 5:15 PM

Answers

  • StSingh, building a column header structure is not something easy to do with TSQL but I tried, I hope this helps. From my experience data display requirements is outside the realm of TSQL developers and usually handled by the software that will consume the query results.

    I hope this helps.  Building ...

    DECLARE
     @cols2 NVARCHAR(MAX)
    ,@query2 NVARCHAR(MAX);

    SELECT @cols2 =
    STUFF((SELECT ',' + QUOTENAME(ImportedDate)
    FROM #Temp_Dataset
    GROUP BY ImportedDate                  
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'');

    SET @query2 =
    'DECLARE @totID MONEY;
    SELECT @totID = COUNT(*) FROM #Temp_Dataset;
    SELECT ActionStatus
    ,' + @cols2 + '
    FROM
    (
     SELECT ActionStatus = ''''
     ,ImportedDate
     ,strRow = ''Cnt'' + ''  ..  '' + ''Pct''
     FROM #Temp_Dataset
     UNION
     SELECT ActionStatus
     ,ImportedDate
     ,strRow = CAST(COUNT(*) AS VARCHAR(5)) + ''      ..  '' + CAST(CAST(COUNT(*) AS MONEY) / @totID AS VARCHAR(5))
     FROM #Temp_Dataset
     GROUP BY ActionStatus,ImportedDate
    ) x
    PIVOT
    (
     MAX(strRow)
     FOR ImportedDate IN
     (' + @cols2 + ')
    ) p
    ';
    --PRINT @query2;
    EXECUTE(@query2);


    SOTATS, Inc.

    • Marked as answer by StSingh Wednesday, November 2, 2016 4:02 PM
    Wednesday, November 2, 2016 2:00 AM

All replies

  • Can you post your input as DDL with insert statements and then desired output? Your narrative is not clear.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 1, 2016 7:41 PM
  • Thanks Naomi for your response I've updated the question with insert statement
    Tuesday, November 1, 2016 8:28 PM
  • Please tell us your expected result from your sample. It will make it more clear. Thanks.
    Tuesday, November 1, 2016 8:48 PM
  • Why did you omit the Cnt, Totals, and %Percent% in your DDL?  It is not clear what you expect as results.

    SOTATS, Inc.

    Tuesday, November 1, 2016 9:02 PM
  • I have update my question and apologies for the missing information.

    I'm not able to add Total and percentage to the output and not sure how would I achieve so its excluded.

    Tuesday, November 1, 2016 10:21 PM
  • I was able to add Union to my dynamic query and get the grand total but not able to add percentage  Column
    Tuesday, November 1, 2016 10:31 PM
  • StSingh, building a column header structure is not something easy to do with TSQL but I tried, I hope this helps. From my experience data display requirements is outside the realm of TSQL developers and usually handled by the software that will consume the query results.

    I hope this helps.  Building ...

    DECLARE
     @cols2 NVARCHAR(MAX)
    ,@query2 NVARCHAR(MAX);

    SELECT @cols2 =
    STUFF((SELECT ',' + QUOTENAME(ImportedDate)
    FROM #Temp_Dataset
    GROUP BY ImportedDate                  
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'');

    SET @query2 =
    'DECLARE @totID MONEY;
    SELECT @totID = COUNT(*) FROM #Temp_Dataset;
    SELECT ActionStatus
    ,' + @cols2 + '
    FROM
    (
     SELECT ActionStatus = ''''
     ,ImportedDate
     ,strRow = ''Cnt'' + ''  ..  '' + ''Pct''
     FROM #Temp_Dataset
     UNION
     SELECT ActionStatus
     ,ImportedDate
     ,strRow = CAST(COUNT(*) AS VARCHAR(5)) + ''      ..  '' + CAST(CAST(COUNT(*) AS MONEY) / @totID AS VARCHAR(5))
     FROM #Temp_Dataset
     GROUP BY ActionStatus,ImportedDate
    ) x
    PIVOT
    (
     MAX(strRow)
     FOR ImportedDate IN
     (' + @cols2 + ')
    ) p
    ';
    --PRINT @query2;
    EXECUTE(@query2);


    SOTATS, Inc.

    • Marked as answer by StSingh Wednesday, November 2, 2016 4:02 PM
    Wednesday, November 2, 2016 2:00 AM
  • You would need one more pivot statement to get percentages across. You can check this link that gives such an example. Considering this is a percentage you can go with using MAX as the operator for pivoting.

    Regards,
    Karthik

    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, November 2, 2016 2:07 AM
  • Hi StSingh,

    To display grouped data and summary information, you could take a look at Create a Matrix in SSRS. Matrices provide functionality similar to crosstabs and pivot tables. You can format the rows and columns to highlight the data you want to emphasize to improve the viewing experience for the user.


    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.

    • Proposed as answer by Riaon Thursday, November 3, 2016 1:44 AM
    Wednesday, November 2, 2016 9:32 AM
  • Thanks Ulysses, I took your advice and snipped out the percentage and total from the T-SQL and placed it on the report.

    Thanks everyone for the help.

    Wednesday, November 2, 2016 4:04 PM