Order the data in columns that are created from multiple CASE statements. Using MySQL-CodePudding

I need to extract data from a table listing the profession and name of people. It is only two columns. The task is to take the data and split it into 4 separate columns according to the profession of the individual and order it by name with NULL values appears last. However each query I have written fails to get the ordering right between the two columns because I cannot separate the ordering between the columns I have created in the CASE statements that I have written. The ordering of the first column in this case Doctor is still linked to the second column Professor and so on. So each time I have tried different ORDER BY statements such as ORDER by -doctor DESC, -professor DESC etc the ordering on the doctor's column impacts the ordering on the professor column causing the NULL values in the professor column to appear above the names in the professor column because they are being influenced by the order of the ORDER BY statement. How do I break this link and order them separately so I can get NULL values to appear last?

Hi I have written the following query below:

SELECT  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;

and it returns:

NULL Ashley NULL NULL

NULL NULL NULL Samantha

Julia NULL NULL NULL

NULL Britney NULL NULL

NULL Maria NULL NULL

NULL Meera NULL NULL

Priya NULL NULL NULL

NULL Priyanka NULL NULL

NULL NULL NULL Jennifer

NULL NULL NULL Ketty

NULL Belvet NULL NULL

NULL Naomi NULL NULL

NULL NULL Jane NULL

NULL NULL Jenny NULL

NULL NULL Kristeen NULL

NULL NULL Christeen NULL

NULL NULL NULL Eve

Aamina NULL NULL NULL

This query:

SELECT 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 -doctor DESC, -professor DESC, -singer DESC, -actor DESC;

Returns this:

Aamina NULL NULL NULL

Julia NULL NULL NULL

Priya NULL NULL NULL

NULL Britney NULL NULL

NULL Maria NULL NULL

NULL Meera NULL NULL

NULL Priyanka NULL NULL

NULL Ashley NULL NULL

NULL Belvet NULL NULL

NULL Naomi NULL NULL

NULL NULL Jane NULL

NULL NULL Jenny NULL

NULL NULL Kristeen NULL

NULL NULL Christeen NULL

NULL NULL NULL Ketty

NULL NULL NULL Eve

NULL NULL NULL Jennifer

NULL NULL NULL Samantha

I need the output to look like below as an example:

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

happy to take solutions in mySQL or SQL.

CodePudding user response：

Test this:

WITH
cte AS ( SELECT name, 
                occupation, 
                ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) rn
         FROM occupations )
SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor, 
       MAX(CASE WHEN occupation = 'Professor' THEN name END) AS Professor, 
       MAX(CASE WHEN occupation = 'Singer' THEN name END) AS Singer, 
       MAX(CASE WHEN occupation = 'Actor' THEN name END) AS Actor
FROM cte
GROUP BY rn
ORDER BY rn;