如何在 MySQL 中透视表 [英] How to pivot tables in MySQL

问题描述

我有一个关于如何在 MySQL 中透视表的问题.我有一个数据集，列如下:

I have a question about how to pivot the table in MySQL. I have a dataset, columns like this:

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...

我想生成一个这样的表:

And I want to generate a table like this:

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

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

Since, this is just a sample from the datasets, so I may not know how much different job titles in the dataset. The goal is to list every person in the different job title columns.

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

Is there any methods to do that? Or is it possible to generate such table in MySQL? An engineer told me that it can done by creating a view, but I do not know how. I read some books, and still confused.

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

Any ideas and SQL queries guides are welcome!

推荐答案

有 3 件事需要考虑 1) 如何动态生成一堆 max(case when 2) 分配一些东西来分组 case when's by - 在这个如果我使用变量生成行号 3)您的某些职位包含空格，我将其删除以生成列标题

There are 3 things to think about 1) How to dynamically generate a bunch of max(case when 2) assigning something to group the case when's by - in this case I generate a row number using a variable 3) some of your job titles contain white space which I remove for the generation of column headers

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

Which can be submitted to dynamic 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)

这篇关于如何在 MySQL 中透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！