Sunday, October 3, 2010

How to Convert column data to rows in teradata

Problem :
There is a requirement to convert column data into rows.
Consider the country table with data as follows

Table has 4 columns (country , value1,value2,value3)
NZ  50  60  70
AUS 110 120 130

The output should be displayed in following manner
country    value
NZ  50
NZ  60
NZ  70
AUS 110
AUS 120
AUS 130

Resulting query:
select country, value1 as value from T_country
union all
select country, value2 as v from T_country
union all
select country, value3 as v from T_country;

There might be various  approaches to this problem.Union was found out to be efficient and served the purpose.

No comments:

Post a Comment