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

问题描述

我有一张这样的桌子

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

然后我想创建这样的报告

+----+---+---+|desc|一月|二月|+----+---+---+|col1|A |C |+----+---+---+|col2|B |A |+----+---+---+|col3|0 |G |+----+---+---+|Col4|B |E |+----+---+---+

有人可以帮忙吗?

解决方案

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

unpivot 或 UNION ALL 片段从 col1、col2 等中获取数据并将其转换为多行:

select id, month, col1 value, 'col1' 描述从你的桌子联合所有选择 id、月份、col2 值、'col2' 描述从你的桌子联合所有选择 ID、月份、col3 值、col3"描述从你的桌子联合所有选择 id、月份、col4 值、'col4' 描述从你的桌子

参见SQL Fiddle with Demo.

结果:

<代码>|身份证 |月 |价值 |描述 |----------------------------------|101 |简 |一个 |列 1 ||102 |二月 |C |列 1 ||101 |简 |乙 |col2 ||102 |二月 |一个 |col2 ||101 |简 |(空) |col3 ||102 |二月 |G |col3 ||101 |简 |乙 |col4 ||102 |二月 |E |col4 |

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

选择描述，max(case when month = 'jan' then value else 0 end) jan,max(case when month = 'feb' then value else 0 end) feb从(选择 id、月份、col1 值、'col1' 描述从你的桌子联合所有选择 id、月份、col2 值、'col2' 描述从你的桌子联合所有选择 ID、月份、col3 值、col3"描述从你的桌子联合所有选择 id、月份、col4 值、'col4' 描述从你的桌子) 源文件按描述分组

参见SQL Fiddle with demo

结果是:

<代码>|描述 |一月 |2 月 |-----------------------|列 1 |一个 |C ||col2 |乙 |一个 ||col3 |0 |G ||col4 |乙 |E |

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?

解决方案

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.

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

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

See SQL Fiddle with demo

The result is:

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

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