mysql - SQL pivot the column values

Question

Problem Statement:

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

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

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

---

My solution:

select Doctor, Professor, Singer, Actor from
(
    select a.name, a.occupation, count(*) as row_num from Occupations a
    join Occupations b 
    on a.occupation=b.occupation and (a.name>=b.name)
    group by a.name, a.occupation
    order by row_num asc;
) as t
PIVOT
(
    max(name)
    for occupation in (Doctor, Professor, Singer, Actor)
)
as pvt;

Error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

Line no 7:

order by row_num asc;

I am trying to learn Advanced sql concepts so as far as now I am able to write the query but I am getting this error. When I run the subquery it works fine and I get some output but after using pivot I get this error. I don't know how to solve it and what's causing this error.

I want to do it by pivot operator and without using row_number.

Subquery:

select a.name, a.occupation, count(*) as row_num from Occupations a
.
.
order by row_num;

Subquery output:

Belvet Professor 1
Jane Singer 1
Jennifer Actor 1
Julia Doctor 1
Britney Professor 2
Priya Doctor 2
Ketty Actor 2
Jenny Singer 2
Maria Professor 3
Samantha Actor 3
Kristeen Singer 3
Meera Professor 4
Naomi Professor 5
Priyanka Professor 6

Answer 1

You can use window functions and conditional aggregation:

select
    rn,
    max(case when occupation = 'Doctor' then name end) doctor,
    max(case when occupation = 'Singer' then name end) singer,
    max(case when occupation = 'Actor'  then name end) actor
from (
    select t.*, row_number() over(partition by occupation order by name) rn
    from mytable t
)
group by rn

The subquery ranks persons having the same occupatin by name. You can then use that information to generate the rows, and access the corresponding name for each occupation with a conditional aggregate.

Without window functions, it is different. If your data is not too large, one option emulates row number with a subquery:

select
    rn,
    max(case when occupation = 'Doctor' then name end) doctor,
    max(case when occupation = 'Singer' then name end) singer,
    max(case when occupation = 'Actor'  then name end) actor
from (
    select t.*, 
        (
            select count(*) 
            from mytable t1 
            where t1.occupation = t.occupation and t1.name <= t.name
        ) rn
    from mytable t
)
group by rn