Load data from denormalized file into a normalized table

Question

I receive a denormalized text file that must be loaded into a normalized table.

Denormalized table:

CustomerID -- Category -- Category2 -- Category3 -- Category4
1 -- A -- B -- C -- D

When this is normalized, it should look like:

CustomerID -- Category
1 -- A
1 -- B
1 -- C
1 -- D

What is the best way to write a T-SQL statement to achieve this (SQL Server 2008)?

Answer 1

Use the UNPIVOT keyword: http://technet.microsoft.com/en-us/library/ms177410.aspx

Naturally you'll want to replace [File] with some sort of OpenRowSet query or use the import/export wizard to get the data into a temp table.

SELECT CustomerId, Category
FROM 
(
   SELECT CustomerId, Category, Category2, Category3, Category4
   FROM [File]

) tblDenormalized
UNPIVOT
(
   Category FOR Column IN 
   (Category, Category2, Category3, Category4)
) AS unpivot;