mysql的行列互转

多行转多列

多行转多列，就是数据库中存在的多条具有一定相同值的行数据，通过提取相同值在列头展示来实现行数据转为列数据，一般提取的值为枚举值。

思路

转换前表样式 ->

转换后表样式 ->

首先，我们知道month是枚举类型，值是固定的，将行数据转换成列数据的时候，我们可以通过对每行数据进行判断，使用case语句对month值进行选择性执行，并将执行结果作为一个列。这样，只要我们把month的所有值均判断执行，就可以实现行项数据转为列项数据。

实现代码

select id,
sum(case when month='Jan' then revenue end) as Jan_Revenue,
sum(case when month='Feb' then revenue end) as Feb_Revenue,
sum(case when month='Mar' then revenue end) as Mar_Revenue,
sum(case when month='Apr' then revenue end) as Apr_Revenue,
sum(case when month='May' then revenue end) as May_Revenue,
sum(case when month='Jun' then revenue end) as Jun_Revenue,
sum(case when month='Jul' then revenue end) as Jul_Revenue,
sum(case when month='Aug' then revenue end) as Aug_Revenue,
sum(case when month='Sep' then revenue end) as Sep_Revenue,
sum(case when month='Oct' then revenue end) as Oct_Revenue,
sum(case when month='Nov' then revenue end) as Nov_Revenue,
sum(case when month='Dec' then revenue end) as Dec_Revenue
from department
group by id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

多列转多行

思路

转换前的样式->

转换后表样式 ->

可以看出，表中存在多个列项值。如果要把列项值转为行项值，首先我们可以考虑的方式就是把每个列转为一行数据，这样可以把所有列均转为独立的行项数据，但此时的行项数据是每个数据一条单独行项记录，并不符合我们所需要的要求，所以最后我们还需要把所有的独立行项数据进行连接。

代码

SELECT id,Jan_Revenue,'Jan_Revenue' as month
from department_row
UNION
SELECT id,Feb_Revenue,'Feb_Revenue' as month
from department_row
UNION
SELECT id,Mar_Revenue,'Mar_Revenue' as month
from department_row
UNION
SELECT id,Apr_Revenue,'Apr_Revenue' as month
from department_row
UNION
SELECT id,May_Revenue,'May_Revenue' as month
from department_row
UNION
SELECT id,Jun_Revenue,'Jun_Revenue' as month
from department_row
UNION
SELECT id,Jul_Revenue,'Jul_Revenue' as month
from department_row
UNION
SELECT id,Aug_Revenue,'Aug_Revenue' as month
from department_row
UNION
SELECT id,Sep_Revenue,'Sep_Revenue' as month
from department_row
UNION
SELECT id,Oct_Revenue,'Oct_Revenue' as month
from department_row
UNION
SELECT id,Nov_Revenue,'Nov_Revenue' as month
from department_row
UNION
SELECT id,Dec_Revenue,'Dec_Revenue' as month
from department_row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35