Mysql将列转换为行(数据透视表) [英] Mysql Convert Column to row (Pivot table )

问题描述

我有一张这样的桌子

+---+-----+----+----+----+----+
|id |month|col1|col2|col3|col4|
+---+-----+----+----+----+----+
|101|Jan  |A   |B   |NULL|B   |
+---+-----+----+----+----+----+
|102|feb  |C   |A   |G   |E   |
+---+-----+----+----+----+----+

然后我要创建这样的报告

And then I want to create report like this

+----+---+---+
|desc|jan|feb|
+----+---+---+
|col1|A  |C  |
+----+---+---+
|col2|B  |A  |
+----+---+---+
|col3|0  |G  |
+----+---+---+
|Col4|B  |E  |
+----+---+---+

任何人都可以帮忙吗?

推荐答案

首先需要做的是先取消数据透视，然后再对其进行透视.但是不幸的是，MySQL没有这些功能，因此您将需要使用UNION ALL查询(用于取消枢纽)和使用聚合功能(使用CASE作为枢轴)来复制它们.

What you need to do is first, unpivot the data and then pivot it. But unfortunately MySQL does not have these functions so you will need to replicate them using a UNION ALL query for the unpivot and an aggregate function with a CASE for the pivot.

unpivot或UNION ALL件从col1，col2等中获取数据，并将其分成多行:

The unpivot or UNION ALL piece takes the data from your col1, col2, etc and turns it into multiple rows:

select id, month, col1 value, 'col1' descrip
from yourtable
union all
select id, month, col2 value, 'col2' descrip
from yourtable
union all
select id, month, col3 value, 'col3' descrip
from yourtable
union all
select id, month, col4 value, 'col4' descrip
from yourtable

请参见带演示的SQL提琴.

结果:

|  ID | MONTH |  VALUE | DESCRIP |
----------------------------------
| 101 |   Jan |      A |    col1 |
| 102 |   feb |      C |    col1 |
| 101 |   Jan |      B |    col2 |
| 102 |   feb |      A |    col2 |
| 101 |   Jan | (null) |    col3 |
| 102 |   feb |      G |    col3 |
| 101 |   Jan |      B |    col4 |
| 102 |   feb |      E |    col4 |

然后将其包装在子查询中以应用聚合，并使用CASE将其转换为所需的格式:

You then wrap this in a subquery to apply the aggregate and the CASE to convert this into the format you want:

select descrip, 
  max(case when month = 'jan' then value else 0 end) jan,
  max(case when month = 'feb' then value else 0 end) feb
from
(
  select id, month, col1 value, 'col1' descrip
  from yourtable
  union all
  select id, month, col2 value, 'col2' descrip
  from yourtable
  union all
  select id, month, col3 value, 'col3' descrip
  from yourtable
  union all
  select id, month, col4 value, 'col4' descrip
  from yourtable
) src
group by descrip

请参见带有演示的SQL小提琴

结果是:

| DESCRIP | JAN | FEB |
-----------------------
|    col1 |   A |   C |
|    col2 |   B |   A |
|    col3 |   0 |   G |
|    col4 |   B |   E |

这篇关于Mysql将列转换为行(数据透视表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！