MySQL 把结果集转置为多行

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

要把行转换为列，根据原表给定列的每个值创建一个列。

例如，返回每个员工及他们的职位(JOB)，目前的查询返回如下结果集:

希望重新设置为结果集的格式，使每个职位使用一列:

二.解决方案

要解决本例的问题，必须使每个job/ename组合唯一，然后，在使用聚集函数去除null时，不会丢失ename。

使用标量子查询，按empno给每个员工分等级。
使用CASE表达式和聚集函数MAX对结果集进行转置变换，同事按子查询的返回值分组:

select max(case when job = 'CLERK'
                then ename else null end) as clerks,
       max(case when job = 'ANALYST'
                then ename else null end) as analysts,
       max(case when job = 'MANAGER'
                then ename else null end) as mgrs,
       max(case when job = 'PRESIDENT'
                then ename else null end) as prez,
       max(case when job = 'SALESMAN'
                then ename else null end) as sales
  FROM (
 SELECT  e.job,
         e.ename,
         (select count(*) from emp d
           where e.job = d.job and e.empno < d.empno) as rnk
   from emp e
        ) x
group by rnk
order by rnk
;

测试记录:

mysql> select max(case when job = 'CLERK'
    ->                 then ename else null end) as clerks,
    ->        max(case when job = 'ANALYST'
    ->                 then ename else null end) as analysts,
    ->        max(case when job = 'MANAGER'
    ->                 then ename else null end) as mgrs,
    ->        max(case when job = 'PRESIDENT'
    ->                 then ename else null end) as prez,
    ->        max(case when job = 'SALESMAN'
    ->                 then ename else null end) as sales
    ->   FROM (
    ->  SELECT  e.job,
    ->          e.ename,
    ->          (select count(*) from emp d
    ->            where e.job = d.job and e.empno < d.empno) as rnk
    ->    from emp e
    ->         ) x
    -> group by rnk
    -> order by rnk
    -> ;
+--------+----------+-------+------+--------+
| clerks | analysts | mgrs  | prez | sales  |
+--------+----------+-------+------+--------+
| MILLER | FORD     | CLARK | KING | TURNER |
| JAMES  | SCOTT    | BLAKE | NULL | MARTIN |
| ADAMS  | NULL     | JONES | NULL | WARD   |
| SMITH  | NULL     | NULL  | NULL | ALLEN  |
+--------+----------+-------+------+--------+
4 rows in set (0.00 sec)