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.
EXAMPLE TABLE 1a: YourTable
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 |
