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