Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Why PIVOT in Sql server output one row only?

Tabl: Occupation (Name, Profession)

Name    Profession
Zahid   Engineer
Rakib   Doctor
David   Singer
Farid   Player
Arif    Doctor
Tarik   Singer

Expected Output:

Doctor Engineer Singer Player
Rakib  Zahid    David  Farid
Arif   NULL     Tarik  NULL

I tried:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Select Doctor, Engineer, Singer, Player 
from 
( Select [Name], Profession from Occupation ) T1
PIVOT
(Max([Name]) for Profession IN ( Doctor, Engineer, Singer, Player)) T2

But, It output only 1 row:

Doctor  Engineer    Singer  Player
Rakib   Zahid   Tarik   Farid

>Solution :

With no GTD of order

Select *
 From  (
        Select [Name]
              ,[Profession]
              ,RN = row_number() over (partition by Profession order by Profession) 
         from Occupation 
       ) src
 Pivot (Max([Name]) for Profession IN ( Doctor, Engineer, Singer, Player)) pvt

Results

RN  Doctor  Engineer    Singer  Player
1   Rakib   Zahid       David   Farid
2   Arif    NULL        Tarik   NULL
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading