Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

1 https://msbiskills.com/2015/09/01/t-sql-query-pivoting-data-puzzle/
2 https://msbiskills.com/2015/10/22/sql-puzzle-simple-pivot-facebook-puzzle/
3 https://msbiskills.com/2016/04/07/sql-puzzle-multiple-column-pivoting-puzzle/
4 https://msbiskills.com/2016/05/26/sql-puzzle-multi-column-pivot-puzzle-gmail/
5 https://msbiskills.com/2016/07/10/sql-puzzle-the-pivot-puzzle-multiple-solutions-best-one/
6 https://msbiskills.com/2016/08/24/sql-puzzle-the-pivot-puzzle-v/
7 https://msbiskills.com/2016/11/28/sql-puzzle-first-and-second-record-pivot-format/
8 https://msbiskills.com/2017/02/14/sql-puzzle-the-pivot-puzzle-vii/
9 https://msbiskills.com/2017/12/03/sql-puzzle-the-pivot-puzzle-vi-is-data-present-for-id-y-blank/
10 https://pawankkmr.wordpress.com/2015/03/24/t-sql-query-year-month-wise-data-the-pivot-puzzle/

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

Advertisements