SQL Server 2005 UNPIVOT Command - changings columns to rows (normalizing)
Scenario
A table that contains several columns that you need to take and change into rows in order to normalize the data. With SQL Server 2005, a new T-SQL command UNPIVOT can help. An example table:
**Table called MyTable
FieldID FieldOne FieldTwo FieldThree
1 abc 3.40 2008-03-03 00:00:00.000
2 def 4.00 2008-01-02 00:00:00.000
**Table Design
FieldID is an integer
FieldOne is char(10)
FieldTwo is decimal(14,2)
FieldThree is a datetime
Sample Code
SELECT FieldId, FieldCode, FieldValue
FROM
(
SELECT FieldId,
CONVERT(varchar(50), RTRIM(FieldOne )) AS FieldOne,
CONVERT(varchar(50), FieldTwo) AS FieldTwo,
CONVERT(varchar(50), FieldThree) AS FieldThree
FROM SampleUnpivot
) MyTable
UNPIVOT
(FieldValue FOR FieldCode IN (FieldOne, FieldTwo, FieldThree))AS MyUnPivot
Results
FieldID FieldCode FieldValue
1 FieldOne abc
1 FieldTwo 3.40
1 FieldThree Mar 3 2008 12:00AM
2 FieldOne def
2 FieldTwo 4.00
2 FieldThree Jan 2 2008 12:00AM
Notes
- If you have differnt data types make sure they are of the same time in the UNPIVOT IN clause
- Make sure to include the "ID" field (FieldID column in example above) to determine original record relationship
