sql列转行 行转列

列转行

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

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

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

示例数据

假设有一个表 Sales：

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

查询

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

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;

结果：

product_id	quarter	sales
1	Q1	100
1	Q2	150
1	Q3	200
1	Q4	250
2	Q1	110
2	Q2	160
2	Q3	210
2	Q4	260

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);

结果：

product_id	quarter	sales
1	Q1	100
1	Q2	150
1	Q3	200
1	Q4	250
2	Q1	110
2	Q2	160
2	Q3	210
2	Q4	260

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

结果：

product_id	quarter	sales
1	Q1	100
1	Q2	150
1	Q3	200
1	Q4	250
2	Q1	110
2	Q2	160
2	Q3	210
2	Q4	260

结论

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

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

行转列

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

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

示例数据

假设有一个表 Sales：

product_id	quarter	sales
1	Q1	100
1	Q2	150
1	Q3	200
1	Q4	250
2	Q1	110
2	Q2	160
2	Q3	210
2	Q4	260

查询

使用 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;

结果：

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

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;

结果与上一个方法相同：

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

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);

结果：

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

结论

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