Dynamically Pivot Columns in T-SQL


By Mark Wojciechowicz, Anexinet Business Intelligence Architect

I recently had an odd request from a client who needed a query which will pivot data from a key – value table.  This is not a normal request in that reporting tools would normally deal with the pivoting of data in a dynamic way, rather than your database tools. Needless to say, this was needed to feed data to an application which was expecting variable columns.

The T-SQL below used the XML Path function to convert the columns to a CSV and then uses the PIVOT function (and dynamic sql) to return the values for each column.

Hope this is helpful to someone!

Create Table #tb (KeyColumn varchar(20), Value int);
INSERT INTO #tb
SELECT ‘Bodington’, 1
UNION All
SELECT ‘Budweiser’, 2
UNION ALl
SELECT ‘Petit_Syrah’, 3
declare
@sql as nvarchar(max),
@cols as nvarchar(max) = (SELECT
STUFF(
(
SELECT
‘,’ + KeyColumn
FROM #tb
FOR XML PATH(”)
), 1, 1, ”
) As concatenated_string)
SET @sql =
‘SELECT ‘+ @cols + ‘
FROM
(
SELECT
KeyColumn
,Value
FROM #tb
) AS tb1
PIVOT (
Max(Value) for KeyColumn IN (‘+ @cols + ‘)
) as pvt’
Exec sp_executesql @sql
DROP TABLE #tb