sql列转行 行转列

大家好，欢迎来到IT知识分享网。

列转行

在 SQL 中，转换数据以按列排列的值成为按行排列的值（即所谓的“列转行”或“列转行”）是常见的数据操作需求。这个操作在不同的数据库管理系统中可以通过不同的技术手段来实现。以下是几种常见的数据库系统中实现列转行的方法：

1. 使用 UNION ALL（适用于所有 SQL 数据库）

如果你的列数是固定的，可以使用 UNION ALL 将多个列的数据合并到一个新的结果集的单一列中。

示例数据

假设有一个表 Sales：

查询

以下是一个将每个季度的销售数据转为行的查询示例：

sql

复制代码

SELECT product_id, 'Q1' AS quarter, sales_q1 AS sales FROM Sales UNION ALL SELECT product_id, 'Q2', sales_q2 FROM Sales UNION ALL SELECT product_id, 'Q3', sales_q3 FROM Sales UNION ALL SELECT product_id, 'Q4', sales_q4 FROM Sales; 

结果：

2. 使用 UNPIVOT（适用于 Oracle 和 SQL Server）

对于支持 UNPIVOT 操作的数据库，如 Oracle 和 SQL Server，可以直接使用 UNPIVOT 函数来实现。

Oracle 示例

sql

复制代码

SELECT product_id, quarter, sales FROM Sales UNPIVOT ( sales FOR quarter IN (sales_q1 AS 'Q1', sales_q2 AS 'Q2', sales_q3 AS 'Q3', sales_q4 AS 'Q4') ); 

SQL Server 示例

sql

复制代码

SELECT product_id, quarter, sales FROM Sales UNPIVOT ( sales FOR quarter IN ([sales_q1], [sales_q2], [sales_q3], [sales_q4]) ) AS Unpvt; 

3. 使用 CROSS APPLY（适用于 SQL Server）

在 SQL Server 中，CROSS APPLY 也可以用于列转行操作。

示例数据

假设有一个表 Sales：

sql

复制代码

CREATE TABLE Sales ( product_id INT, sales_q1 INT, sales_q2 INT, sales_q3 INT, sales_q4 INT ); INSERT INTO Sales (product_id, sales_q1, sales_q2, sales_q3, sales_q4) VALUES (1, 100, 150, 200, 250), (2, 110, 160, 210, 260); 

查询

sql

复制代码

SELECT product_id, quarter, sales FROM Sales CROSS APPLY (VALUES ('Q1', sales_q1), ('Q2', sales_q2), ('Q3', sales_q3), ('Q4', sales_q4) ) AS Quarters (quarter, sales); 

结果：

4. 使用 LATERAL VIEW（适用于 Hive）

在 Hive 中，LATERAL VIEW 和 explode 函数可以实现类似的操作。

示例数据

假设有一个表 Sales：

plaintext

复制代码

product_id | sales_q1 | sales_q2 | sales_q3 | sales_q4 1 | 100 | 150 | 200 | 250 2 | 110 | 160 | 210 | 260 

查询

sql

复制代码

SELECT product_id, quarter, sales FROM Sales LATERAL VIEW explode(array( named_struct('quarter', 'Q1', 'sales', sales_q1), named_struct('quarter', 'Q2', 'sales', sales_q2), named_struct('quarter', 'Q3', 'sales', sales_q3), named_struct('quarter', 'Q4', 'sales', sales_q4) )) T AS quarter_sales 

结果：

结论

• UNION ALL：适用于所有 SQL 数据库，适用于小规模、列数固定的情况。
• UNPIVOT：适用于 Oracle 和 SQL Server，适用于需要灵活处理列转行数据的情况。
• CROSS APPLY：适用于 SQL Server，提供了更灵活的列转行操作。
• LATERAL VIEW：适用于 Hive，处理大规模数据集合的列转行。

在 SQL 中，行转列（也称为“透视”或“旋转”）的操作是将行数据转换为列数据，这在数据总结和分析中非常有用。不同的数据库系统有不同的方法来实现行转列的操作。以下是一些常见数据库系统中的行转列技术和示例。

行转列

1. 使用 CASE 和 GROUP BY（适用于所有 SQL 数据库）

这种方法适用于所有支持 SQL 的数据库，适用于数据量不大的简单场景。

示例数据

假设有一个表 Sales：

查询

使用 CASE 语句和 GROUP BY 来实现行转列：

sql

复制代码

SELECT product_id, SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS sales_q1, SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS sales_q2, SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS sales_q3, SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS sales_q4 FROM Sales GROUP BY product_id; 

结果：

2. 使用 PIVOT（适用于 Oracle 和 SQL Server）

对于支持 PIVOT 操作的数据库，如 Oracle 和 SQL Server，可以直接使用 PIVOT 函数来实现。

Oracle 示例

sql

复制代码

SELECT * FROM ( SELECT product_id, quarter, sales FROM Sales ) PIVOT ( SUM(sales) FOR quarter IN ('Q1' AS sales_q1, 'Q2' AS sales_q2, 'Q3' AS sales_q3, 'Q4' AS sales_q4) ); 

SQL Server 示例

sql

复制代码

SELECT product_id, sales_q1, sales_q2, sales_q3, sales_q4 FROM ( SELECT product_id, quarter, sales FROM Sales ) AS SourceTable PIVOT ( SUM(sales) FOR quarter IN ([Q1], [Q2], [Q3], [Q4]) ) AS PivotTable; 

结果与上一个方法相同：

3. 使用动态 SQL（适用于列名不固定的情况）

当需要处理列名不固定的情况时，可以使用动态 SQL。这里以 SQL Server 为例：

动态 SQL 示例

sql

复制代码

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); -- 获取所有的quarter值，并用逗号拼接起来 SELECT @columns = STRING_AGG(QUOTENAME(quarter), ', ') FROM (SELECT DISTINCT quarter FROM Sales) AS Quarters; -- 创建动态SQL语句 SET @sql = ' SELECT product_id, ' + @columns + ' FROM ( SELECT product_id, quarter, sales FROM Sales ) AS SourceTable PIVOT ( SUM(sales) FOR quarter IN (' + @columns + ') ) AS PivotTable'; -- 执行动态SQL EXEC sp_executesql @sql; 

这种方法可以处理列名不固定的情况，动态生成列名列表，并执行 PIVOT 操作。

4. 在 PostgreSQL 中使用 crosstab

在 PostgreSQL 中，可以使用 crosstab 函数来进行行转列操作。首先需要安装 tablefunc 扩展：

sql

复制代码

CREATE EXTENSION IF NOT EXISTS tablefunc; 

然后，可以使用 crosstab 函数：

示例数据

sql

复制代码

CREATE TABLE Sales ( product_id INT, quarter TEXT, sales INT ); INSERT INTO Sales (product_id, quarter, sales) VALUES (1, 'Q1', 100), (1, 'Q2', 150), (1, 'Q3', 200), (1, 'Q4', 250), (2, 'Q1', 110), (2, 'Q2', 160), (2, 'Q3', 210), (2, 'Q4', 260); 

查询

sql

复制代码

SELECT * FROM crosstab( 'SELECT product_id, quarter, sales FROM Sales ORDER BY product_id, quarter', 'SELECT DISTINCT quarter FROM Sales ORDER BY quarter' ) AS ct (product_id INT, sales_q1 INT, sales_q2 INT, sales_q3 INT, sales_q4 INT); 

结果：

结论

• CASE 和 GROUP BY：适用于所有 SQL 数据库，适合数据量不大的简单场景。
• PIVOT：适用于 Oracle 和 SQL Server，适合需要灵活处理列转行数据的情况。
• 动态 SQL：适用于列名不固定的情况，通过动态生成 SQL 语句实现。
• crosstab：适用于 PostgreSQL，通过 tablefunc 扩展实现行转列操作。