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

Comments