如何在mysql pivot中动态添加列并按空分组?

我有一张表,上面有姓名和职业的值。我试着做两件事

旋转工作台(已实现)
根据值动态添加/删除列
减少透视后列中的空值

我的疑问是:

select Actor, Teacher, Doctor from (
SELECT
(case when occupation = (select distinct occupation from occs a order by 1 limit 0,1) then name else NULL end) as "Actor"
,(case when occupation = (select distinct occupation from occs a order by 1 limit 1,1) then name else NULL end) as "Teacher"
,(case when occupation = (select distinct occupation from occs a order by 1 limit 2,1) then name else NULL end) as "Doctor"
FROM occs order by 1) x ;

上述查询的输出:

+-------+---------+--------+
| Actor | Teacher | Doctor |
+-------+---------+--------+
| NULL  | NULL    | ketty  |
| NULL  | Jane    | NULL   |
| NULL  | Zhan    | NULL   |
| julia | NULL    | NULL   |
| meera | NULL    | NULL   |
+-------+---------+--------+

我想把输出作为

+-------+---------+--------+
| Actor | Teacher | Doctor |
+-------+---------+--------+
| julia | Jane    | ketty  |
| meera | Zhan    | NULL   |
+-------+---------+--------+

另外,在我的sql中,我指定了列actor、teacher和doctor。有没有一种方法可以扫描表并获取列,而不必手动执行DISTINCT。

问题的DDL/DML:

CREATE TABLE occs (
  name varchar(10) DEFAULT NULL,
  occupation varchar(10));


insert into occs values ('julia','Actor');
insert into occs values ('meera','Actor');
insert into occs values ('ketty','Teacher');
insert into occs values ('Jane','Doctor');
insert into occs values ('Zhan','Doctor');

WITH cte AS ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation) AS rownum FROM occs) SELECT MAX(CASE WHEN occupation = 'Actor' THEN name END) AS Actor, MAX(CASE WHEN occupation = 'Teacher' THEN name END) AS Teacher, MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor FROM cte GROUP BY rownum