Home » Tip Bank » XML » Data Management

Language: SQL
Expertise: Intermediate

Aug 15, 2003

Say you wanted to transfer data from an unnormalized source table in Excel (a worksheet) to SQL Server.

Take a look at the columns in the source table, TABLE_A, with repeating groups of data as columns. Assume that columns Mark1, Mark2 and Mark3 stand for Subjects 'S1', 'S2' and 'S3'.

TABLE_A: Student_ID, StudentName, Mark1, Mark2, Mark3

You have to transfer this data to normalized table TABLE_B with following columns:

TABLE_B: Student_ID, StudentName, Mark_ID, Marks

Use the following SQL in SQL Server:

Insert into TABLE_B (Student_ID, StudentName,  Mark_ID, Marks)
Select Student_ID, StudentName, 'S1',  Mark1
From TABLE_A
Union All
Select Student_ID, StudentName, 'S2',  Mark2
From TABLE_A
Union All
Select Student_ID, StudentName, 'S3',  Mark3
From TABLE_A
Order by 1,2,3,4

You will have a Union of as many SELECT as the number of the repeating groups of data (the repeating columns) in TABLE_A.

This SQL can be used in any database.