--


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



--

--

/*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)



--

--

/*------------------------
/*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)


--