Convert columns to rows.

Analytics
Enthusiast

Convert columns to rows.

Hi ,

I have a table with data like :

US 110 120 130
UK 50 60 70

I want data to be :

US 110
US 120
US 130
UK 50
UK 60
UK 70

The reason i want to bring all values into a single column is that I can use rank fn based on country. Please let me know if there can be a better way.

Thanks!
2 REPLIES
Enthusiast

Re: Convert columns to rows.

try this

select country, v1 as v from T
union all
select country, v2 as v from T
union all
select country, v3 as v from T

probably not the most efficient but it should get the job done

Enthusiast

Re: Convert columns to rows.

you can also try this

with recursive count3 (country, ctr) as
( select country, 1 as ctr
from countries
union all
select c2.country, c1.ctr+1 as ctr
from count3 as c1 join countries as c2 on c1.country = c2.country and c1.ctr < 3
)
select c1.country,
case when ctr = 1 then v1 when ctr = 2 then v2 when ctr = 3 then v3 end as val,
ctr
from count3 as c1 join countries as c2 on c1.country = c2.country
order by c1.country, ctr;