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
SELECT ‘Budweiser’, 2
SELECT ‘Petit_Syrah’, 3
@sql as nvarchar(max),
@cols as nvarchar(max) = (SELECT
‘,’ + KeyColumn
FOR XML PATH(”)
), 1, 1, ”
) As concatenated_string)
SET @sql =
‘SELECT ‘+ @cols + ‘
) AS tb1
Max(Value) for KeyColumn IN (‘+ @cols + ‘)
) as pvt’
Exec sp_executesql @sql