Rotating a table in SQL

It was interesting to me how they let inexperienced people design and create databases!. one of my friend’s friends owns a company in the health field. they created a database like

customer ID year1 year2
1 1400 1600
2 1300 1500

where year1 is 2008, year2 is 2009 , and so on. Nice! isn’t it ? now i have to correct this disaster with huge amount of data in the database.

i have to create a new table that would look like

customer ID year value
1 2008 1400
1 2009 1600

The select statement that would generate the correct output would be
SELECT 'customer ID','2008' as year,year1 as value
UNION
SELECT 'customer ID','2009' as year,year2 as value
UNION
SELECT 'customer ID','2010' as year,year3 as value
UNION
.
.

now we have to enclose that into a create as statement but create as is not a good friend with union so i had to do a funny trick

Create table newtable as SELECT * from
(SELECT 'customer ID','2008' as year,year1 as value
UNION
SELECT 'customer ID','2009' as year,year2 as value
UNION
SELECT 'customer ID','2010' as year,year3 as value
UNION
.
.
)

It is funny for me that SQL Server can not handle the (create as select union) and you have to make select from select.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s