September 2009

So, the other day I was trying to pivot some SQL data that was basically in this format…

RECNUMBER	COLNUMBER	FIELDNAME	FIELDVALUE
1	1	FirstName	Davin
1	2	LastName	Studer
2	1	FirstName	Melissa
2	2	LastName	Studer

I wanted to see it like this …

RECNUMBER	FIRSTNAME	LASTNAME
1	Davin	Studer
2	Melissa	Studer

It took me a while to figure out the syntax for this, but here it is. It uses the SQL pivot keyword.

This first portion dynamically creates the column names to be used in the pivot.

declare @cols varchar(max)
 
select @cols = stuff (
    (
        select '], [' + FieldName
        from table
        group by FieldName
        order by min(ColNumber)
        for
        xml path('')
    ),
    1,
    2,
    ''
) + ']'

This next part creates a query within a string that we will run with the SQL exec command.

declare @query varchar(max)
 
set @query = '
    select RecNumber, ' + @cols + '
    from (
        select RecNumber, FieldName, FieldValue
        from table
    ) dta
    pivot
    (
        min(FieldValue)
        for FieldName in (' + @cols + ')
    ) pvt
'

All that is left to do is to execute the query that was created. I ended up putting this into a stored procedure.

exec(@query)