mysql - 如何在 MySQL 中对表进行透视

Question

我有一个关于如何在 MySQL 中旋转表的问题。我有一个数据集，像这样的列:

ID   Name     job_title
1    Sam       Fireman
2    Tomas     Driver
3    Peter     Fireman
4    Lisa      Analyst
5    Marcus    Postman
6    Stephan   Analyst
7    Mary      Research Manager
8    Albert    Analyst
9    Chen      Driver
...etc...

我想生成一个这样的表:

Fireman  Driver   Analyst  Postman   Research Manager ...
Sam     Tomas     Lisa     Marcus     Mary
Peter   Chen      Stephan  (someone)  (someone)...
....etc...

因为这只是数据集中的样本，所以我可能不知道数据集中有多少不同的职位。目标是列出不同职位列中的每个人。

有什么方法可以做到吗？或者是否可以在 MySQL 中生成这样的表？一位工程师告诉我可以通过创建 View 来完成，但我不知道如何做。看了一些书，还是一头雾水。

欢迎任何想法和 SQL 查询指南!

Answer 1

有 3 件事需要考虑 1) 如何动态生成一堆最大值(case when 2)分配一些东西来分组 case when's by - 在这种情况下我使用变量生成行号 3)你的一些职位名称包含空白，我在生成列标题时将其删除

set @sql = 
            (select concat('select ', gc,            ' from 
             (select name,job_title,
                if (job_title <> @p, @rn:=1 ,@rn:=@rn+1) rn,
                @p:=job_title p
                from t
                cross join (select @rn:=0,@p:=null) r
                order by job_title
              ) s group by rn;') from
            (select 
                group_concat('max(case when job_title = ', char(39),job_title ,char(39),' then name else char(32) end ) as ',replace(job_title,char(32),'')) gc
                from
                (
                select distinct job_title from t
                ) s
                ) t
             )
;           

生成这段sql代码

select max(case when job_title = 'Fireman' then name else char(32) end ) as Fireman,
        max(case when job_title = 'Driver' then name else char(32) end ) as Driver,
        max(case when job_title = 'Analyst' then name else char(32) end ) as Analyst,
        max(case when job_title = 'Postman' then name else char(32) end ) as Postman,
        max(case when job_title = 'Research Manager' then name else char(32) end ) as ResearchManager
         from 
             (select name,job_title,
                if (job_title <> @p, @rn:=1 ,@rn:=@rn+1) rn,
                @p:=job_title p
                from t
                cross join (select @rn:=0,@p:=null) r
                order by job_title
              ) s group by rn;

可以提交动态sql

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

结果

+---------+--------+---------+---------+-----------------+
| Fireman | Driver | Analyst | Postman | ResearchManager |
+---------+--------+---------+---------+-----------------+
| Sam     | Tomas  | Lisa    | Marcus  | Mary            |
| Peter   | Chen   | Stephan |         |                 |
|         |        | Albert  |         |                 |
+---------+--------+---------+---------+-----------------+
3 rows in set (0.00 sec)