Show all varchar values with PIVOT

I need to show all varchar values with pivot but I’m not sure if that is possible?

I have OCCUPATIONS table:

-----------+------------+
|   NAME    | OCCUPATION |
+-----------+------------+
| Eve       | Actor      |
| Jennifer  | Actor      |
| Ketty     | Actor      |
| Samantha  | Actor      |
| Aamina    | Doctor     |
| Julia     | Doctor     |
| Priya     | Doctor     |
| Ashley    | Professor  |
| Belvet    | Professor  |
| Britney   | Professor  |
| Maria     | Professor  |
| Meera     | Professor  |
| Naomi     | Professor  |
| Priyanka  | Professor  |
| Christeen | Singer     |
| Jane      | Singer     |
| Jenny     | Singer     |
| Kristeen  | Singer     |
+-----------+------------+

Pivot query:

    select NAME, OCCUPATION from OCCUPATIONS 
    ) t
    PIVOT( MAX(NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
    ) as PIVOT_TABLE;

Query result:

+--------+-----------+----------+----------+
| Doctor | Professor |  Singer  |  Actor   |
+--------+-----------+----------+----------+
| Priya  | Priyanka  | Kristeen | Samantha |
+--------+-----------+----------+----------+

Above query gives only 1 record in each column but I want to get all.

Answer

You ‘ll need an extra column, e.g. with ROW_NUMBER():

SELECT RN, [Doctor], [Professor], [Singer], [Actor]
FROM  
(SELECT NAME, OCCUPATION ,ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME ) as RN
FROM OCCUPATIONS 
) t
PIVOT( MAX(NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
) as PIVOT_TABLE;