All Forums General
Purushotham 87 posts Joined 04/14
06 Jul 2014
how to transpose rows to columns, I tried i am getting the results, but id, name, salary titles are not displaying

Hi everyone,
I have a table with columns:
ID   Name    Salary
10  puru      50000
20  ravi       45000
30  suresh   40000
I want output as : 
ID      10   20  30
Name  puru  ravi  suresh
Salary 50000  45000  40000
By using below query i am getting ouput as :   10    20       30
                                                               puru    ravi    suresh
                                                             50000  45000  40000

 

But i need  to display:  id     10     20         30
                             name  puru   ravi      suresh
                             salary50000   45000    40000

 

 

Select 

Max(Case Row_no when 1 then id else null end) 

,Max(Case Row_no when 2 then id else null end) 

,Max(Case Row_no when 3 then id else null end) 

from (select id, row_number() over(order by id) as Row_no from tablename1) T1 

Union All 

Select 

Max(Case Row_no when 1 then name else null end) 

,Max(Case Row_no when 2 then name else null end) 

,Max(Case Row_no when 3 then name else null end) 

from (select name, row_number() over(order by name) as Row_no from tablename1) T2 

Union All 

Select 

Max(Case Row_no when 1 then salary else null end) 

,Max(Case Row_no when 2 then salary else null end) 

,Max(Case Row_no when 3 then salary else null end) 

from (select salary, row_number() over(order by salary) as Row_no from tablename1) T3 

 

Could anyone pls help me...

 

Regards,

purushotham

 

Purushotham.M
Raja_KT 1246 posts Joined 07/09
06 Jul 2014

Did you also try something like this in your query:
'id    '|| Max(Case Row_no when 1 then id else null end)  ......

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
06 Jul 2014

Hi raja,

Thank u for giving solution and quick response..

Regards,
Purushotham.

Purushotham.M

You must sign in to leave a comment.