All Forums Analytics
adash-7422 33 posts Joined 01/09
05 Feb 2010
Convert columns to rows.

Hi ,I have a table with data like :US 110 120 130UK 50 60 70I want data to be :US 110US 120US 130UK 50UK 60UK 70The 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!

red ronkonkoma 4 posts Joined 09/09
09 Feb 2010

try thisselect country, v1 as v from Tunion allselect country, v2 as v from Tunion allselect country, v3 as v from Tprobably not the most efficient but it should get the job done

red ronkonkoma 4 posts Joined 09/09
09 Feb 2010

you can also try thiswith 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;

You must sign in to leave a comment.