Transposing rows into columns fields with a dynamic SQL query. (Normalize the table)

I have always thought that it seemed to require an extreme number of ludicrously difficult SQL operations to do a simple transpose of rows into columns for repeating data tagged by a primary key. Many suggestions to fix this involve doing a cross tab, or exporting the data to excel and then constructing a pivot table, cube or a data pilot (or whatever it may be called in your application software).

Then you realise the pivot is not the answer as you have fields where it is not necessary to compute an aggregate function for the summary for a crosstab aka datapilot or pivot table.

Very often this is not required if you have a thoughtful DBA with correctly pre-designed Normalized DB tables.

However in the real world you will find that if you have gathered data say from an importing an excel spreadsheet, your tables will be often be in the de-normalized form.

Or the data in columns is dynamically collected through some web forms.
You might simply have forgotten to normalize the table before hand and realized your mistake when you started to write that complex Query. Now that the data is collected you can not dump it all and start over. You may want to do the normalization at the end to try and avoid inserting temporary nulls in your records.

Whatever the reason here is a a simple SQL statement construct, to re-construct a table by transposing the values non-primary key rows, grouping by the foreign key. Voila 3NF (third normal form).


SELECT ID,
MAX(CASE WHEN ItemNumber=1 THEN SomeFieldItemValue ELSE NULL END) AS [Item1],
MAX(CASE WHEN ItemNumber=2 THEN SomeFieldItemValue ELSE NULL END) AS [Item2],
MAX(CASE WHEN ItemNumber=3 THEN SomeFieldItemValue ELSE NULL END) AS [Item3],
MAX(CASE WHEN ItemNumber=4 THEN SomeFieldItemValue ELSE NULL END) AS [Item4],
MAX(CASE WHEN ItemNumber=5 THEN SomeFieldItemValue ELSE NULL END) AS [Item5]
FROM Table INTO NewTable
GROUP BY ID

EXAMPLE TABLE 1a: YourTable

ID (DEPARTMENT)	ItemNumber (Year)	SomeFieldItemValue (Sales Color Code)
1240	2009	Blue
1240	2010	Red
1240	2011	Red
3620	2009	Green
3620	2010	Green
3620	2011	Blue

Above is Your Table transformed to the New Table below

EXAMPLE TABLE 1b: newTable

ID (DEPARTMENT)	2009 (Item1)	2010 (Item2)	2011 (Item N)
1240	Blue	Red	Red
3620	Green	Green	Blue