SQL Puzzle | Dynamic Pivot Puzzle

In this puzzle you have to dynamically pivot the data. For more details please check the sample input and expected output.

Sample Input

R Command KeyAction
PK1 1 Power
PK1 2 Volume Up
PK1 3 Volume Down
PK1 4 Pause
PK1 6 Play
PK1 3 TV Volume Up
PK1 4 TV Volume Down
PK2 7 Power
PK2 2 Volume Up

Expected Output

R ButtonCount 1 2 3 4 6 7
PK1 7 Power Volume Up TV Volume Up Pause Play NULL
PK2 2 NULL Volume Up NULL NULL NULL Power

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE DynamicPivot ( R varchar(20) , Command smallint , KeyAction varchar( 20 ) ) GO insert INTO DynamicPivot values ('PK1', 1, 'Power') , ('PK1', 2, 'Volume Up') , ('PK1', 3, 'Volume Down') , ('PK1', 4, 'Pause') , ('PK1', 6, 'Play') , ('PK1', 3, 'TV Volume Up') , ('PK1', 4, 'TV Volume Down') GO insert INTO DynamicPivot values ('PK2', 7, 'Power') , ('PK2', 2, 'Volume Up') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- /*Declare the variable to get the comma separated string for new columns*/ DECLARE @DistinctCommands AS VARCHAR(MAX) = '' DECLARE @MaxDistinctCommands AS VARCHAR(MAX) = '' /*Get data in the variable*/ SELECT @DistinctCommands = @DistinctCommands + '[' + CAST(Command AS VARCHAR(100)) + ']' + ',' ,@MaxDistinctCommands = @MaxDistinctCommands + 'MAX([' + CAST(Command AS VARCHAR(100)) + ']) ' + '[' + CAST(Command AS VARCHAR(100)) + ']' + ',' FROM DynamicPivot GROUP BY Command /*Remove the Last comma*/ SET @DistinctCommands = SUBSTRING(@DistinctCommands,0,LEN(@DistinctCommands)) SET @MaxDistinctCommands = SUBSTRING(@MaxDistinctCommands,0,LEN(@MaxDistinctCommands)) /*Check the Variable Data*/ --SELECT @DistinctCommands --SELECT @MaxDistinctCommands DECLARE @SQL AS VARCHAR(MAX) = '' /*Generate Dynamic SQL*/ SET @SQL = ' ;WITH CTE AS ( SELECT t.R,COUNT(*) ButtonCount FROM DynamicPivot t GROUP BY t.r ) ,CTE1 AS ( SELECT c.ButtonCount,t.Command,c.R,t.KeyAction FROM CTE c INNER JOIN DynamicPivot t ON c.R = t.R ) SELECT R,ButtonCount,' + @MaxDistinctCommands + ' FROM CTE1 r PIVOT ( MIN(r.keyaction) FOR [Command] IN ( ' + @DistinctCommands + ') )t GROUP BY r,ButtonCount ' /*Execute Dynamic SQL*/ EXEC(@SQL) -- ```

Output-1

 ```-- /*------------------------ /*Declare the variable to get the comma separated string for new columns*/ DECLARE @DistinctCommands AS VARCHAR(MAX) = '' DECLARE @MaxDistinctCommands AS VARCHAR(MAX) = '' /*Get data in the variable*/ SELECT @DistinctCommands = @DistinctCommands + '[' + CAST(Command AS VARCHAR(100)) + ']' + ',' ,@MaxDistinctCommands = @MaxDistinctCommands + 'MAX([' + CAST(Command AS VARCHAR(100)) + ']) ' + '[' + CAST(Command AS VARCHAR(100)) + ']' + ',' FROM DynamicPivot GROUP BY Command /*Remove the Last comma*/ SET @DistinctCommands = SUBSTRING(@DistinctCommands,0,LEN(@DistinctCommands)) SET @MaxDistinctCommands = SUBSTRING(@MaxDistinctCommands,0,LEN(@MaxDistinctCommands)) /*Check the Variable Data*/ --SELECT @DistinctCommands --SELECT @MaxDistinctCommands DECLARE @SQL AS VARCHAR(MAX) = '' /*Generate Dynamic SQL*/ SET @SQL = ' ;WITH CTE AS ( SELECT t.R,COUNT(*) ButtonCount FROM DynamicPivot t GROUP BY t.r ) ,CTE1 AS ( SELECT c.ButtonCount,t.Command,c.R,t.KeyAction FROM CTE c INNER JOIN DynamicPivot t ON c.R = t.R ) SELECT R,ButtonCount,' + @MaxDistinctCommands + ' FROM CTE1 r PIVOT ( MIN(r.keyaction) FOR [Command] IN ( ' + @DistinctCommands + ') )t GROUP BY r,ButtonCount ' /*Execute Dynamic SQL*/ EXEC(@SQL) ------------------------*/ R ButtonCount 1 2 3 4 6 7 -------------------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- PK1 7 Power Volume Up TV Volume Up Pause Play NULL PK2 2 NULL Volume Up NULL NULL NULL Power (2 row(s) affected) -- ```

Other Reference Puzzles for Pivoting

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com