Monthly Archives: September 2009

Pivoting SQL Data

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.

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

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