How to use pivot in sql server (without aggregates )?

Question

please help me solve this problem: You are given a table, containing two columns: column is one of the followings:

Doctor
Professor
Singer
Actor

Write a query to output the names underneath the corresponding occ. in the following format:

+--------+-----------+--------+------+

| Doctor | Professor | Singer | Actor|

+--------+-----------+--------+------+

Names must be listed in alphabetically sorted order.

Sample Input

Name        Occupation
Meera       Singer
Ashely      Professor
Ketty       Professor
Christeen   Professor
Jane        Actor
Jenny       Doctor
Priya       Singer    

Sample Output

Jenny    Ashley     Meera  Jane

Samantha Christeen  Priya  Julia

NULL     Ketty      NULL   Maria

Note

Print "NULL" when there are no more names corresponding to an occupation.

I tried using :

SELECT *
FROM
(
SELECT [Name], [Occupation] 
FROM occupations 
) AS source
PIVOT
(
    max([Name])
    FOR [occupation] IN ([Doctor], [Professor], [Singer], [Actor]) 
) as pvt;

which gives the following output:

Priya Priyanka Kristeen Samantha 

How to fix it ?

Answer 1

You just need to give each name a row number based on their occupation and order alphabetically.. then include that row number in your pivot query.

CREATE TABLE Occupations (
     NAME VARCHAR(MAX),
     Occupation VARCHAR(MAX)
    )
INSERT  INTO Occupations
VALUES
        ('Samantha','Doctor'),
        ('Julia','Actor'),
        ('Maria','Actor'),
        ('Meera','Singer'),
        ('Ashley','Professor'),
        ('Ketty','Professor'),
        ('Christeen','Professor'),
        ('Jane','Actor'),
        ('Jenny','Doctor'),
        ('Priya','Singer');

SELECT
    [Doctor],
    [Professor],
    [Singer],
    [Actor]
FROM
    (SELECT 
         ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) rn,
         [Name],
         [Occupation] 
     FROM 
         Occupations
    ) AS source 
PIVOT
    MAX(Name) FOR [occupation] IN ([Doctor],[Professor],[Singer],[Actor]) as pvt
ORDER BY rn


DROP TABLE Occupations

Answer 2

SELECT [Doctor], [Professor], [Singer], [Actor] FROM   
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) ROW_NO, 
    ISNULL(NULL,Name) as Name, Occupation
    FROM Occupations
) AS t 
PIVOT(
    MAX(Name)
    FOR Occupation IN (
        [Doctor], 
        [Professor], 
        [Singer], 
        [Actor]
    )
) AS pivot_table
ORDER BY ROW_NO;

Answer 3

I tried this in Oracle, seemed easier to comprehend:

SELECT min(Doctor), min(Professor), min(Singer), min(Actor)
FROM
( Select
ROW_NUMBER() OVER (PARTITION BY Occupation order by Name) rn, 
CASE 
WHEN Occupation = 'Doctor' then Name
end as Doctor,
CASE 
WHEN Occupation = 'Professor' then Name
end as Professor,
CASE 
WHEN Occupation = 'Singer' then Name
end as Singer,
CASE 
WHEN Occupation = 'Actor' then Name
end as Actor
from OCCUPATIONS
order by Name) a
group by rn
order by rn;