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

Question

我有这样一个表格

id	month	col1	col2	col3	col4
101	1月	A	B	NULL	B
102	2月	C	A	G	E

然后我想创建如下报告

desc	1月	2月
col1	A	C
col2	B	A
col3	0	G
Col4	B	E

有人可以帮忙吗？

- user1914516

---

欢迎来到stackoverflow。这是一个非常常见的问题。请花几分钟时间搜索存档。首先尝试适应以前的答案。如果遇到问题，请在此处发布您的查询和任何错误信息。 - Leigh

可能是MySQL将行转换为动态列的重复问题。 - RichardTheKiwi

Answer 1

你需要做的是首先将数据进行"unpivot"，然后再进行"pivot"。但不幸的是，MySQL没有这些函数，因此您需要使用UNION ALL查询复制它们以进行"unpivot"操作，使用聚合函数与CASE实现"pivot"。

"Unpivot"或UNION ALL部分将来自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

请查看带有演示的SQL Fiddle。

结果：

|  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语句将其转换为所需的格式：

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 Fiddle

结果如下：

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

- Taryn

---

谢谢你！非常有帮助。 - Moe Chughtai

非常感谢。如果 yourtable 是来自子查询的派生表，那我是不是要用类似 FROM (SELECT * FROM table WHERE name = 'condition') t1 替换每个 yourtable？ - Accountant م

@会计师م 是的，那正是你要做的。 - Taryn

@Taryn - 你的解决方案真是太棒了。这对我帮助很大。投了你的答案一票，太棒了。 - Ishpreet

Answer 2

尽管这个问题非常古老，而且有人标记它为“非常普遍”，但人们似乎仍在找到它（包括我），并且发现它有用。我开发了一个更通用的版本来解压行，并认为它可能对某些人有帮助。

SET @target_schema='schema';
SET @target_table='table';
SET @target_where='`id`=1';
SELECT
    GROUP_CONCAT(qry SEPARATOR ' UNION ALL ')
    INTO @sql
FROM (
    SELECT
        CONCAT('SELECT `id`,', QUOTE(COLUMN_NAME), ' AS `key`,`', COLUMN_NAME, '` AS `value` FROM `', @target_table, '` WHERE ', @target_where) qry
    FROM (
        SELECT `COLUMN_NAME` 
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA`=@target_schema 
            AND `TABLE_NAME`=@target_table
    ) AS `A`
) AS `B`;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

我在MySQL 8.x服务器上使用此查询并对其进行聚合，因此结果结构为id，key，value的JSON对象。

- belowtoxic

Answer 3

(扩展这个很棒的之前的答案，因为我目前的账户无法进行编辑或评论)

虽然这种方法不像之前的 UNION ALL / CASE - 方法那样直接，但它的优点在于它可以被用于（“动态地”）任意数量的原始列 [请纠正我关于“任意”的理解]。

可能会导致不清晰错误的限制是

group_concat_max_len系统变量，默认值为1024

在这种情况下，只需尝试一些类似于

SET SESSION group_concat_max_len = 92160;

- Haris

---

这并没有回答问题。一旦您拥有足够的声望，您将能够评论任何帖子；相反，提供不需要询问者澄清的答案。- 来自审核 - Pedro Paiva