mySQL数据透视表.正在删除NULL值

Question

所以我必须透视一个表，并用null替换所有缺失的值。当我试图对新列进行排序时，问题就开始了。所有的null值都在开头，我有没有办法以某种方式修剪列的总长度或排除null，直到达到第一个名称？

问题是：在"职业"中透视"职业"列，使每个名称按字母顺序排序，并显示在其相应的"职业"下面。输出列标题应分别为Doctor、Professor、Singer和Actor。

注意：当不再有与某个职业对应的名称时，打印NULL。

表格概述：https://i.stack.imgur.com/48zB1.png

样本输入为：https://i.stack.imgur.com/LLh5g.png

到目前为止我尝试了什么：

SELECT  
CASE WHEN occupation = 'Doctor' THEN name  END AS col1,
CASE WHEN occupation = 'Professor' THEN name END as col2,
CASE WHEN occupation = 'Singer' THEN name END as col3,
CASE WHEN occupation = 'Actor' THEN name END as col4
FROM OCCUPATIONS
GROUP BY occupation,name
order by col1,col2,col3,col4 ASC;

我得到的回复：

NULL NULL NULL Eve
NULL NULL NULL Jennifer
NULL NULL NULL Ketty
NULL NULL NULL Samantha
NULL NULL Christeen NULL
NULL NULL Jane NULL
NULL NULL Jenny NULL
NULL NULL Kristeen NULL
NULL Ashley NULL NULL
NULL Belvet NULL NULL
NULL Britney NULL NULL
NULL Maria NULL NULL
NULL Meera NULL NULL
NULL Naomi NULL NULL
NULL Priyanka NULL NULL
Aamina NULL NULL NULL
Julia NULL NULL NULL
Priya NULL NULL NULL

我想要返回的示例：

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

Answer 1

分配一个行号并按其分组

drop table if exists t;
create table t(name varchar(1), occupation varchar(2));
insert into t values
('b','dr'),('a','dr'),('c','dr'),
('z','ok'),('a','ok');

with cte as
(select name, occupation, 
row_number() over (partition by occupation order by name) rn
from t)
select   rn,
max(case when occupation = 'dr' then name else null end) 'dr',
max(case when occupation = 'ok' then name else null end) 'ok'
from cte
group by rn;
+----+------+------+
| rn | dr   | ok   |
+----+------+------+
|  1 | a    | a    |
|  2 | b    | z    |
|  3 | c    | NULL |
+----+------+------+
3 rows in set (0.023 sec)