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.
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
Thank you :
- kareem