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