SQL Server - Dynamic Pivoting

In this post, let us see an example for dynamic pivoting.

CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
INSERT INTO TMP SELECT 'SQL Server',1
INSERT INTO TMP SELECT 'Oracle',2

--Example for dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'') 
   
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;

--adding 1 more row & checking dynamic pivoting
INSERT INTO TMP SELECT 'DB2',3

--Dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'') 
   
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;

See Also:

• My posts on T-SQL