Sql列转行

在SQL中，将列数据转换为行数据的过程通常称为**列转行**（也称为反透视）。这种转换常用于将宽表结构转换为长表结构，以便进行数据分析或报表生成。以下是几种常见的实现方法，适用于不同的数据库系统。 ### 1. 使用 `UNION ALL` 实现列转行 这是最基础、最通用的方法，适用于几乎所有支持 `UNION` 操作的数据库系统（如 SQL Server、MySQL、Oracle 等）。 假设有一个成绩表 `Score`，其结构如下： | Name | 数学 | 语文 | 英文 | |-------|------|------|------| | 张三 | 90 | 85 | 95 | | 李四 | 88 | 92 | 89 | 要将这三门课程的成绩转换为行数据，可以使用如下 SQL 语句： ```sql SELECT Name, '数学' AS Subject, 数学 AS Score FROM Score UNION ALL SELECT Name, '语文' AS Subject, 语文 AS Score FROM Score UNION ALL SELECT Name, '英文' AS Subject, 英文 AS Score FROM Score ORDER BY Name; ``` 该查询将生成如下结果： | Name | Subject | Score | |-------|---------|-------| | 张三 | 数学 | 90 | | 张三 | 语文 | 85 | | 张三 | 英文 | 95 | | 李四 | 数学 | 88 | | 李四 | 语文 | 92 | | 李四 | 英文 | 89 | 该方法在 SQL Server 中被广泛使用，并且可以结合 `CASE` 语句对排序进行控制 [^3]。 --- ### 2. 使用 `CROSS APPLY`（适用于 SQL Server） `CROSS APPLY` 是 SQL Server 提供的一种表值函数调用机制，也可以用于列转行操作。 ```sql SELECT Name, Subject, Score FROM Score CROSS APPLY ( VALUES ('数学', 数学), ('语文', 语文), ('英文', 英文) ) AS Subjects(Subject, Score) ORDER BY Name; ``` 这种方法语法简洁，执行效率较高，适用于多个列的转换场景。 --- ### 3. 使用 `UNPIVOT`（适用于 SQL Server 和 Oracle） `UNPIVOT` 是 SQL Server 提供的专门用于列转行的语法结构。 ```sql SELECT Name, Subject, Score FROM Score UNPIVOT ( Score FOR Subject IN (数学, 语文, 英文) ) AS Unpvt ORDER BY Name; ``` 该方法结构清晰，适合字段数量较多的情况，但不支持聚合操作。 --- ### 4. 使用 `LISTAGG` 和 `REGEXP_SUBSTR` 实现列转行（适用于 Oracle） 如果原始数据中存在多个列，且每列包含多个值（如逗号分隔字符串），可以先使用 `LISTAGG` 合并，再通过正则表达式拆分实现列转行。 例如，先使用 `LISTAGG` 合并城市信息 [^2]，再通过 `REGEXP_SUBSTR` 拆分字符串实现行数据输出。 ```sql WITH temp AS ( SELECT 'China' nation, 'Guangzhou' city FROM dual UNION ALL SELECT 'China', 'Shanghai' FROM dual UNION ALL SELECT 'China', 'Beijing' FROM dual ) SELECT nation, REGEXP_SUBSTR(cities, '[^,]+', 1, LEVEL) AS city FROM ( SELECT nation, LISTAGG(city, ',') WITHIN GROUP (ORDER BY city) AS cities FROM temp GROUP BY nation ) CONNECT BY LEVEL <= REGEXP_COUNT(cities, ',') + 1; ``` --- ### 5. 使用 `json_each_table`（适用于 PostgreSQL） PostgreSQL 提供了 JSON 函数来处理列转行问题，尤其是当列值存储为 JSON 对象时。 ```sql SELECT id, key AS subject, value::int AS score FROM your_table, LATERAL json_each_text(to_jsonb(your_table) - 'id'); ``` 该方法适用于动态列结构，且能够处理复杂的嵌套数据。 --- ### 总结 | 方法 | 适用数据库 | 特点 | |--------------|------------------|----------------------------------| | `UNION ALL` | 所有支持 SQL 的数据库 | 通用性强，但写法繁琐 | | `CROSS APPLY`| SQL Server | 语法简洁，性能较好 | | `UNPIVOT` | SQL Server、Oracle | 专为列转行设计，结构清晰 | | `LISTAGG` + 正则 | Oracle | 适用于字符串拼接后拆分的场景 | | `JSON` 函数 | PostgreSQL | 适用于 JSON 类型字段的动态处理 | 根据具体的数据库类型和数据结构，可以选择最适合的列转行方法。 ---