Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Aug 15, 2003

Populate a Normalized Table with Data from an Unnormalized Table

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.

Rajender Dhanwada
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap