SQL Server 2005 UNPIVOT Command – changings columns to rows (normalizing)

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
  SELECT FieldId,
    CONVERT(varchar(50), RTRIM(FieldOne )) AS FieldOne,
    CONVERT(varchar(50), FieldTwo) AS FieldTwo,
    CONVERT(varchar(50), FieldThree) AS FieldThree
  FROM SampleUnpivot
) MyTable
FieldValue FOR FieldCode IN (FieldOne, FieldTwo, FieldThree))AS MyUnPivot


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


  • 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 (3)

  1. Hunain Shaikh says:

    Thank You!!!…Really good example on Unpivot operator.  Very helpful in getting my task done…

Skip to main content