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:
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
CodePudding user response:
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
CodePudding user response:
Perhaps what you need is an arbitrary ROW_NUMBER
value in your derived table:
WITH Occupation AS(
SELECT *
FROM(VALUES('Zahid','Engineer'),
('Rakib','Doctor'),
('David','Singer'),
('Farid','Player'),
('Arif ','Doctor'),
('Tarik','Singer'))V([Name],Profession))
SELECT --P.RN,
P.Doctor,
P.Engineer,
P.Singer,
P.Player
FROM (SELECT [Name],
Profession,
ROW_NUMBER() OVER (PARTITION BY Profession ORDER BY [Name]) AS RN
FROM Occupation) O
PIVOT
(Max([Name]) for Profession IN ( Doctor, Engineer, Singer, Player)) P;