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)