how to transpose rows to columns, I tried i am getting the results, but id, name, salary titles are not displaying

General
Enthusiast

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

2 REPLIES
Enthusiast

Re: how to transpose rows to columns, I tried i am getting the results, but id, name, salary titles are not displaying

Did you also try something like this in your query:

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

Enthusiast

Re: how to transpose rows to columns, I tried i am getting the results, but id, name, salary titles are not displaying

Hi raja,

Thank u for giving solution and quick response..

Regards,
Purushotham.