Mysql 将列转换为行（数据透视表）

Mysql Convert Column to row (Pivot table )

提问by user1914516

I have a table like this

我有一张这样的桌子

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

Can anyone help with this?

有人能帮忙吗？

回答by Taryn

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 ALLquery for the unpivot and an aggregate function with a CASEfor the pivot.

您需要做的是首先对数据进行反透视，然后对其进行透视。但不幸的是 MySQL 没有这些函数，因此您需要使用UNION ALLunpivot 查询和带有 a 的聚合函数来复制它们CASE。

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

unpivot 或UNION ALLpiece 从col1、col2 等中获取数据并将其转换为多行：

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

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

Result:

结果：

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

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

然后将其包装在子查询中以应用聚合并将其CASE转换为您想要的格式：

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

See SQL Fiddle with demo

请参阅带有演示的 SQL Fiddle

The result is:

结果是：

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