如何在 MySQL 中逆透视和透视数据以将列转换为行？

MySQL 数据解透视与透视：将列转换为行

假设您有一个包含多个列（col1、col2、col3 等）的表，并且希望将其转换为透视表格式，以行为单位而不是列来呈现数据。

在 MySQL 中实现此目标，首先使用 UNION ALL 查询进行数据解透视。此过程从原始列值创建多个行：

<code class="language-sql">SELECT id, month, col1 AS value, 'col1' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col2 AS value, 'col2' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col3 AS value, 'col3' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col4 AS value, 'col4' AS descrip
FROM yourtable;</code>

UNION ALL 查询的结果：

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

接下来，将 UNION ALL 查询包装在一个子查询中以透视数据。使用聚合函数 GROUP BY 和 CASE 语句将解透视的结构转换为所需的透视格式：

<code class="language-sql">SELECT descrip,
MAX(CASE WHEN month = 'jan' THEN value ELSE 0 END) AS jan,
MAX(CASE WHEN month = 'feb' THEN value ELSE 0 END) AS feb
FROM (
    SELECT id, month, col1 AS value, 'col1' AS descrip
    FROM yourtable
    UNION ALL
    SELECT id, month, col2 AS value, 'col2' AS descrip
    FROM yourtable
    UNION ALL
    SELECT id, month, col3 AS value, 'col3' AS descrip
    FROM yourtable
    UNION ALL
    SELECT id, month, col4 AS value, 'col4' AS descrip
    FROM yourtable
) AS src
GROUP BY descrip;</code>

透视查询的结果：

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

此过程有效地将表结构从列转换为行，使您可以以更简洁明了的方式呈现数据。

以上是如何在 MySQL 中逆透视和透视数据以将列转换为行？的详细内容。更多信息请关注PHP中文网其他相关文章！