SQL行转列（Pivot）操作作为数据转换的核心技术，在实际业务中扮演着“数据重塑”的关键角色，能够将分散的行数据转化为直观的列结构，在报表生成、数据分析中不可或缺。掌握其语法可高效处理多维度数据，大幅提升数据可读性和分析效率。
SQL 行转列（Pivot）操作主要通过条件聚合实现，核心是利用CASE WHEN语句结合聚合函数（如SUM、MAX）将行数据转换为列。以下主要介绍其通用语法和实现方式，仅供参考：（所有表格和代码块都可左右滚动）

一、通用语法

行转列（Pivot）是将行数据转换为列数据的操作，标准SQL没有直接提供特定语法（PIVOT关键字，部分数据库如SQL Server支持），但可通过条件聚合实现：

SELECT
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    -- 其他月份...
FROM revenue_data
GROUP BY id;

1.1 语法结构

行转列的基本语法结构包含以下几个部分：

SELECT
    分组列,
    聚合函数(CASE WHEN 条件 THEN 值 END) AS 列别名1,
    聚合函数(CASE WHEN 条件 THEN 值 END) AS 列别名2,
    ...
FROM 表名
GROUP BY 分组列;

其中：

• 分组列：通常是唯一标识记录的列，如用户ID、产品ID等
• 聚合函数：常用SUM、MAX、MIN、COUNT等，用于合并多行数据
• CASE WHEN：条件判断表达式，用于筛选符合条件的数据
• 列别名：转换后新列的名称

1.2 语法元素

行转列操作涉及的核心语法元素包括：

1. SELECT：用于指定查询结果集的列
2. CASE：条件分支语句，是行转列的核心
3. WHEN/THEN：CASE语句中的条件判断和返回值
4. END：CASE语句的结束标记
5. 聚合函数：常用的聚合函数包括：
6. SUM()：求和
7. MAX()/MIN()：取最大值/最小值
8. COUNT()：计数
9. AVG()：求平均值
10. GROUP BY：指定分组依据
11. COALESCE()：空值处理函数，将NULL转换为指定值

1.3 执行流程

行转列的执行流程可以分为以下几个步骤：

1. 数据分组：根据GROUP BY子句对数据进行分组
2. 条件筛选：对每个分组内的数据应用CASE WHEN条件
3. 聚合计算：对筛选后的数据应用聚合函数
4. 结果合并：将每个分组的计算结果合并为最终结果

以示例SQL为例，执行流程如下：

-- 示例查询
SELECT
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

假设有以下原始数据：

id | month | revenue
---+-------+--------
101| Jan   | 100
101| Feb   | 150
102| Jan   | 200
102| Mar   | 50

执行流程分析：

1. 按id分组：
2. 组1：id=101 (Jan:100, Feb:150)
3. 组2：id=102 (Jan:200, Mar:50)
4. 对每个组应用条件：
5. Jan条件：匹配200 → SUM(200) = 200
6. Feb条件：无匹配 → SUM(NULL) = NULL
7. Mar条件：匹配50 → SUM(50) = 50
8. Jan条件：匹配100 → SUM(100) = 100
9. Feb条件：匹配150 → SUM(150) = 150
10. Mar条件：无匹配 → SUM(NULL) = NULL
11. 组1：
12. 组2：
13. 结果合并：

id | Jan_Revenue | Feb_Revenue | Mar_Revenue
---+-------------+-------------+-------------
101| 100         | 150         | NULL
102| 200         | NULL        | 50

1.4 不同数据库的实现差异

虽然基本原理相同，但不同数据库对行转列的支持方式存在差异：

• 通用方法：所有数据库都支持使用CASE+聚合函数的方式
• 特定语法：部分数据库提供专用的PIVOT关键字
• 扩展函数：某些数据库提供辅助函数简化操作

下面是不同数据库的实现对比：

-- SQL Server PIVOT语法
SELECT id, Jan_Revenue, Feb_Revenue, Mar_Revenue
FROM revenue_data
PIVOT (
    SUM(revenue)
    FOR month IN ([Jan], [Feb], [Mar])
) AS pvt;

-- Oracle PIVOT语法
SELECT *
FROM revenue_data
PIVOT (
    SUM(revenue)
    FOR month IN ('Jan' AS Jan_Revenue, 'Feb' AS Feb_Revenue, 'Mar' AS Mar_Revenue)
);

-- PostgreSQL crosstab函数
SELECT *
FROM crosstab(
    'SELECT id, month, revenue FROM revenue_data ORDER BY 1,2',
    $VALUES ('Jan'), ('Feb'), ('Mar')$
) AS ct (id int, Jan_Revenue numeric, Feb_Revenue numeric, Mar_Revenue numeric);

二、模拟场景示例（举例介绍行转列操作）

2.1 创建测试数据表（前置SQL，可以飘过）

下面创建一个用于测试行转列操作的示例表，并插入一些测试数据：

-- 创建收入数据表
CREATE TABLE revenue_data (
    id INT,
    month CHAR(3),  -- 月份缩写
    revenue DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO revenue_data VALUES
(101, 'Jan', 100.00),
(101, 'Feb', 150.00),
(101, 'Mar', 200.00),
(102, 'Jan', 250.00),
(102, 'Mar', 150.00),
(103, 'Feb', 300.00),
(103, 'Mar', 250.00);

2.2 基本行转列查询示例

使用基本的行转列语法对上述数据进行转换：

SELECT
    id,
    COALESCE(SUM(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Revenue
FROM revenue_data
GROUP BY id
ORDER BY id;

结果分析：

id | Jan_Revenue | Feb_Revenue | Mar_Revenue
---+-------------+-------------+-------------
101| 100.00      | 150.00      | 200.00
102| 250.00      | 0.00        | 150.00
103| 0.00        | 300.00      | 250.00

• 每个ID对应一行结果
• 使用COALESCE函数确保没有数据的月份显示为0而不是NULL
• 每个月的收入数据被转换为横向的列

2.3 多值场景示例

当同一ID在同一月份有多个记录时，行转列操作会自动聚合这些值：

-- 插入额外测试数据
INSERT INTO revenue_data VALUES
(101, 'Jan', 50.00),  -- 101在1月有两条记录
(103, 'Mar', 150.00); -- 103在3月有两条记录

-- 重新执行行转列查询
SELECT
    id,
    COALESCE(SUM(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Revenue
FROM revenue_data
GROUP BY id
ORDER BY id;

结果分析：

id | Jan_Revenue | Feb_Revenue | Mar_Revenue
---+-------------+-------------+-------------
101| 150.00      | 150.00      | 200.00
102| 250.00      | 0.00        | 150.00
103| 0.00        | 300.00      | 400.00

• 101在1月的收入从100变为150（100 + 50）
• 103在3月的收入从250变为400（250 + 150）
• SUM函数自动聚合了同一ID在同一月份的多条记录

2.4 不同聚合函数示例

根据业务需求，可以选择不同的聚合函数：

-- 使用MAX替代SUM（假设每月只有一条记录）
SELECT
    id,
    COALESCE(MAX(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue,
    COALESCE(MAX(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Revenue,
    COALESCE(MAX(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

-- 使用COUNT统计记录数
SELECT
    id,
    COALESCE(COUNT(CASE WHEN month = 'Jan' THEN 1 END), 0) AS Jan_Count,
    COALESCE(COUNT(CASE WHEN month = 'Feb' THEN 1 END), 0) AS Feb_Count,
    COALESCE(COUNT(CASE WHEN month = 'Mar' THEN 1 END), 0) AS Mar_Count
FROM revenue_data
GROUP BY id;

-- 使用AVG计算平均值
SELECT
    id,
    COALESCE(AVG(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Avg,
    COALESCE(AVG(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Avg,
    COALESCE(AVG(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Avg
FROM revenue_data
GROUP BY id;

2.5 业务场景示例：电商平台月度销售报表（后面单开）

行转列在实际业务中有广泛的应用场景，例如电商平台月度销售报表：

-- 前置SQL
-- 创建销售数据表
CREATE TABLE sales_data (
    merchant_id INT,
    month CHAR(3),
    sales_amount DECIMAL(10,2)
);

-- 插入示例数据
INSERT INTO sales_data VALUES
(1, 'Jan', 10000.00),
(1, 'Feb', 12000.00),
(1, 'Mar', 15000.00),
(2, 'Jan', 8000.00),
(2, 'Feb', 9000.00),
(3, 'Mar', 11000.00);

-- 生成月度销售报表
SELECT
    merchant_id,
    COALESCE(SUM(CASE WHEN month = 'Jan' THEN sales_amount END), 0) AS Jan_Sales,
    COALESCE(SUM(CASE WHEN month = 'Feb' THEN sales_amount END), 0) AS Feb_Sales,
    COALESCE(SUM(CASE WHEN month = 'Mar' THEN sales_amount END), 0) AS Mar_Sales,
    COALESCE(SUM(sales_amount), 0) AS Total_Sales
FROM sales_data
GROUP BY merchant_id
ORDER BY merchant_id;

结果分析：

merchant_id | Jan_Sales | Feb_Sales | Mar_Sales | Total_Sales
------------+-----------+-----------+-----------+-------------
1           | 10000.00  | 12000.00  | 15000.00  | 37000.00
2           | 8000.00   | 9000.00   | 0.00      | 17000.00
3           | 0.00      | 0.00      | 11000.00  | 11000.00

• 横向展示每个商家在各月的销售额
• 新增Total_Sales列计算总销售额
• 使用COALESCE确保无销售数据的月份显示为0

三、高级应用场景示例

3.1 处理多年度数据

当数据包含多年度信息时，需要在行转列时同时考虑年份和月份：

-- 创建包含年份的数据表
CREATE TABLE revenue_data_year (
    id INT,
    year INT,
    month CHAR(3),
    revenue DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO revenue_data_year VALUES
(101, 2023, 'Jan', 100.00),
(101, 2023, 'Feb', 150.00),
(101, 2024, 'Jan', 200.00),
(102, 2023, 'Jan', 250.00),
(102, 2024, 'Mar', 150.00);

-- 行转列查询（按年-月组合）
SELECT
    id,
    COALESCE(SUM(CASE WHEN year = 2023 AND month = 'Jan' THEN revenue END), 0) AS "2023_Jan",
    COALESCE(SUM(CASE WHEN year = 2023 AND month = 'Feb' THEN revenue END), 0) AS "2023_Feb",
    COALESCE(SUM(CASE WHEN year = 2024 AND month = 'Jan' THEN revenue END), 0) AS "2024_Jan",
    COALESCE(SUM(CASE WHEN year = 2024 AND month = 'Mar' THEN revenue END), 0) AS "2024_Mar"
FROM revenue_data_year
GROUP BY id
ORDER BY id;

结果分析：

id | 2023_Jan | 2023_Feb | 2024_Jan | 2024_Mar
---+----------+----------+----------+----------
101| 100.00   | 150.00   | 200.00   | 0.00
102| 250.00   | 0.00     | 0.00     | 150.00

3.2 使用JSON动态列

在某些场景下，使用JSON格式返回动态列更加灵活：

-- PostgreSQL示例：生成JSON格式动态列
SELECT
    id,
    jsonb_object_agg(CONCAT(year, '_', month), revenue) AS yearly_monthly_revenue
FROM revenue_data_year
GROUP BY id;

-- 输出结果示例：
-- id | yearly_monthly_revenue
-- ---+-------------------------
-- 101| {"2023_Jan": 100, "2023_Feb": 150, "2024_Jan": 200}
-- 102| {"2023_Jan": 250, "2024_Mar": 150}

JSON格式的优点：

• 无需预先定义列名
• 可以动态扩展，适应任意数量的年份和月份
• 便于前端处理和展示

3.3 动态生成列名

当需要转换的列不确定时，可以使用动态SQL生成查询：

-- SQL Server动态生成列名示例
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT(year, '_', month)) 
                      FROM revenue_data_year 
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 'SELECT id, ' + @cols + ' 
              FROM (
                  SELECT id, CONCAT(year, ''_'', month) AS year_month, revenue
                  FROM revenue_data_year
              ) AS src
              PIVOT (
                  SUM(revenue)
                  FOR year_month IN (' + @cols + ')
              ) AS pvt';

EXEC sp_executesql @query;

结果分析：

id | 2023_Jan | 2023_Feb | 2024_Jan | 2024_Mar
---+----------+----------+----------+----------
101| 100.00   | 150.00   | 200.00   | 0.00
102| 250.00   | 0.00     | 0.00     | 150.00

四、优化建议与避坑指南

4.1 性能优化

当处理大量数据时，行转列操作可能会变得缓慢。以下是一些性能优化建议：

1. 创建复合索引：为分组列和条件列创建复合索引，加速数据检索

-- 创建复合索引
CREATE INDEX idx_revenue ON revenue_data(id, month, revenue);

1. 使用物化视图：对于静态或定期更新的数据，使用物化视图预计算结果

-- PostgreSQL示例
CREATE MATERIALIZED VIEW monthly_revenue_pivot AS
SELECT
    id,
    COALESCE(SUM(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Revenue,
    COALESCE(SUM(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW monthly_revenue_pivot;

1. 分区表：对于历史数据，考虑按时间分区存储

-- PostgreSQL示例：按月份分区
CREATE TABLE revenue_data (
    id INT,
    month CHAR(3),
    revenue DECIMAL(10,2)
) PARTITION BY LIST (month);

CREATE TABLE revenue_data_jan PARTITION OF revenue_data
    FOR VALUES IN ('Jan');
CREATE TABLE revenue_data_feb PARTITION OF revenue_data
    FOR VALUES IN ('Feb');
-- 其他月份表...

4.2 空值处理

在行转列操作中，空值处理非常重要，否则可能导致结果不符合预期：

1. 危险做法：直接使用聚合函数而不处理NULL

-- 危险：可能导致NULL值
SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue

1. 正确方案：始终使用COALESCE函数处理NULL值

-- 正确：确保NULL被转换为0
COALESCE(SUM(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue

4.3 动态列陷阱

静态SQL写法无法适应动态变化的列，例如新增月份时需要修改SQL：

/* 错误：静态写法无法适应新增月份 */
SELECT ... Feb_Revenue, Mar_Revenue...

解决方案：

1. 应用层动态生成SQL：根据实际数据动态生成查询语句
2. 使用数据库特性：如PostgreSQL的crosstab函数或SQL Server的PIVOT语法

-- PostgreSQL动态列示例
SELECT *
FROM crosstab(
    'SELECT id, month, revenue FROM revenue_data ORDER BY 1,2',
    $VALUES ('Jan'), ('Feb'), ('Mar')$
) AS ct (id int, Jan_Revenue numeric, Feb_Revenue numeric, Mar_Revenue numeric);

4.4 数据完整性检查

在行转列之前，建议进行数据完整性检查，确保数据质量：

1. 检查月份拼写：防止因拼写错误导致数据丢失

-- 查看所有不同的月份值
SELECT DISTINCT month FROM revenue_data;

1. 验证重复数据：确保每个ID每个月只有一条记录（如果业务需要）

-- 查找重复记录
SELECT id, month, COUNT(*) 
FROM revenue_data 
GROUP BY id, month 
HAVING COUNT(*) > 1;

4.5 怎样选择聚合函数？

根据业务需求正确选择聚合函数：

1. SUM()：当存在多行相同月份数据时，需要累加值
2. MAX()/MIN()：当每月仅单条记录时，可使用MAX/MIN提高性能
3. AVG()：需要计算平均值时
4. COUNT()：需要统计记录数时

-- 示例：使用MAX替代SUM（当每月仅单条记录时）
SELECT
    id,
    COALESCE(MAX(CASE WHEN month = 'Jan' THEN revenue END), 0) AS Jan_Revenue,
    COALESCE(MAX(CASE WHEN month = 'Feb' THEN revenue END), 0) AS Feb_Revenue,
    COALESCE(MAX(CASE WHEN month = 'Mar' THEN revenue END), 0) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

五、实际业务场景示例

以下结合具体业务场景、模拟数据及操作案例，来说明SQL行转列（Pivot）操作的应用：

5.1 销售数据报表生成

业务痛点：企业销售数据通常按“日期-区域-销售额”纵向存储（每行一条记录），但管理层需要横向对比各区域/产品的月度业绩（列级展示）。
场景示例：某电商平台2024年Q1各区域销售额数据，需转化为“区域×月份”的透视表。

模拟数据

-- 前置SQL
CREATE TABLE sales_data (
    region VARCHAR(20),  -- 销售区域
    month VARCHAR(10),   -- 月份
    amount DECIMAL(10,2) -- 销售额
);

INSERT INTO sales_data VALUES
('华东', '2024-01', 50000),
('华东', '2024-02', 62000),
('华北', '2024-01', 45000),
('华北', '2024-03', 58000),
('华南', '2024-02', 38000),
('华南', '2024-03', 42000);

行转列操作

SELECT
    region,
    -- 转换为“月份列”，用COALESCE处理空值为0
    COALESCE(SUM(CASE WHEN month = '2024-01' THEN amount END), 0) AS "2024-01",
    COALESCE(SUM(CASE WHEN month = '2024-02' THEN amount END), 0) AS "2024-02",
    COALESCE(SUM(CASE WHEN month = '2024-03' THEN amount END), 0) AS "2024-03"
FROM sales_data
GROUP BY region;

转换结果

价值：横向对比各区域在不同月份的业绩差异，便于快速定位销售高峰/低谷区域，为资源调配提供数据支持。

5.2 用户行为分析

业务痛点：用户行为日志按“用户-行为类型-次数”纵向记录，需统计每个用户的各类行为频次（如点击、收藏、购买），用于用户画像构建。
场景示例：某APP 2024年7月用户行为数据，需转化为“用户×行为类型”的频次表。

模拟数据

-- 前置SQL
CREATE TABLE user_behavior (
    user_id INT,
    behavior_type VARCHAR(20),  -- 行为类型：click, collect, purchase
    count INT                   -- 行为次数
);

INSERT INTO user_behavior VALUES
(1001, 'click', 25),
(1001, 'collect', 8),
(1001, 'purchase', 3),
(1002, 'click', 18),
(1002, 'purchase', 2),
(1003, 'collect', 12);

行转列操作

SELECT
    user_id,
    -- 用SUM统计行为次数，无记录则为0
    COALESCE(SUM(CASE WHEN behavior_type = 'click' THEN count END), 0) AS click_count,
    COALESCE(SUM(CASE WHEN behavior_type = 'collect' THEN count END), 0) AS collect_count,
    COALESCE(SUM(CASE WHEN behavior_type = 'purchase' THEN count END), 0) AS purchase_count
FROM user_behavior
GROUP BY user_id;

转换结果

价值：快速识别高价值用户（如1001用户点击、收藏、购买均活跃）和潜在流失用户（如1003用户仅收藏未购买），为精准运营提供依据。

5.3 库存管理与供应链分析

业务痛点：库存数据按“商品-仓库-库存量”纵向存储，需横向对比同一商品在不同仓库的库存分布，避免缺货或积压。
场景示例：某零售企业2024年Q2商品库存数据，需转化为“商品×仓库”的库存表。

模拟数据

-- 前置SQL
CREATE TABLE inventory (
    product_id VARCHAR(10),
    warehouse VARCHAR(20),  -- 仓库：北京、上海、广州
    stock_quantity INT      -- 库存量
);

INSERT INTO inventory VALUES
('P001', '北京仓', 150),
('P001', '上海仓', 200),
('P002', '北京仓', 80),
('P002', '上海仓', 120),
('P002', '广州仓', 90),
('P003', '广州仓', 300);

行转列操作

SELECT
    product_id,
    -- 用MAX获取库存量（每个商品在每个仓库仅一条记录）
    COALESCE(MAX(CASE WHEN warehouse = '北京仓' THEN stock_quantity END), 0) AS 北京仓库存,
    COALESCE(MAX(CASE WHEN warehouse = '上海仓' THEN stock_quantity END), 0) AS 上海仓库存,
    COALESCE(MAX(CASE WHEN warehouse = '广州仓' THEN stock_quantity END), 0) AS 广州仓库存
FROM inventory
GROUP BY product_id;

转换结果

价值：直观发现库存分布问题（如P001在广州仓无库存，可能影响华南地区销售），辅助供应链部门调整调拨策略。

5.4 人力资源数据分析

业务痛点：员工考勤数据按“员工-月份-请假天数”纵向记录，需统计每个员工的月度请假情况，用于绩效考核。
场景示例：某公司2024年Q2员工请假数据，需转化为“员工×月份”的请假表。

模拟数据

-- 前置SQL
CREATE TABLE employee_leave (
    emp_id INT,
    month VARCHAR(10),  -- 月份：2024-04, 2024-05, 2024-06
    leave_days DECIMAL(3,1)  -- 请假天数（含半天）
);

INSERT INTO employee_leave VALUES
(2001, '2024-04', 1.0),
(2001, '2024-06', 2.5),
(2002, '2024-05', 1.0),
(2003, '2024-04', 0.5),
(2003, '2024-05', 3.0),
(2003, '2024-06', 1.0);

行转列操作

SELECT
    emp_id,
    -- 用SUM计算月度总请假天数（支持同一月多次请假）
    COALESCE(SUM(CASE WHEN month = '2024-04' THEN leave_days END), 0) AS "2024-04_leave",
    COALESCE(SUM(CASE WHEN month = '2024-05' THEN leave_days END), 0) AS "2024-05_leave",
    COALESCE(SUM(CASE WHEN month = '2024-06' THEN leave_days END), 0) AS "2024-06_leave"
FROM employee_leave
GROUP BY emp_id;

转换结果

价值：快速识别请假频繁的员工（如2003员工Q2累计请假4.5天），为考勤管理和团队排班提供数据支持。

5.5 多维度数据聚合（跨表行转列）

业务痛点：企业数据分散在多张表（如销售表、成本表），需合并后按多维度横向展示（如“区域×月份”的销售额与成本），用于利润分析。
场景示例：某企业2024年Q1销售与成本数据，需合并为“区域×月份”的利润表（利润=销售额-成本）。

模拟数据

-- 前置SQL
-- 销售表
CREATE TABLE sales (
    region VARCHAR(20),
    month VARCHAR(10),
    sales_amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
('华东', '2024-01', 50000),
('华东', '2024-02', 62000),
('华北', '2024-01', 45000);

-- 成本表
CREATE TABLE costs (
    region VARCHAR(20),
    month VARCHAR(10),
    cost_amount DECIMAL(10,2)
);
INSERT INTO costs VALUES
('华东', '2024-01', 25000),
('华东', '2024-02', 31000),
('华北', '2024-01', 22500);

行转列操作（跨表聚合）

-- 先关联表，再行转列
WITH merged_data AS (
    SELECT 
        s.region,
        s.month,
        s.sales_amount,
        c.cost_amount
    FROM sales s
    LEFT JOIN costs c 
        ON s.region = c.region AND s.month = c.month
)
SELECT
    region,
    -- 销售额行转列
    COALESCE(SUM(CASE WHEN month = '2024-01' THEN sales_amount END), 0) AS "2024-01_sales",
    COALESCE(SUM(CASE WHEN month = '2024-02' THEN sales_amount END), 0) AS "2024-02_sales",
    -- 成本行转列
    COALESCE(SUM(CASE WHEN month = '2024-01' THEN cost_amount END), 0) AS "2024-01_cost",
    COALESCE(SUM(CASE WHEN month = '2024-02' THEN cost_amount END), 0) AS "2024-02_cost",
    -- 计算利润（销售额-成本）
    (COALESCE(SUM(CASE WHEN month = '2024-01' THEN sales_amount END), 0) -
     COALESCE(SUM(CASE WHEN month = '2024-01' THEN cost_amount END), 0)) AS "2024-01_profit"
FROM merged_data
GROUP BY region;

转换结果

价值：整合多表数据，横向展示“销售额-成本-利润”全链路指标，为区域利润分析和决策提供一站式数据支持。

行转列操作在具体业务场景的价值总结

1. 提升数据可读性：将纵向分散的数据转化为横向对比表，符合人类阅读和分析习惯；
2. 支撑业务决策：在销售、运营、供应链等场景中，提供直观的多维度对比数据；
3. 适配报表工具：多数BI工具（如Tableau、Power BI）需列级数据作为输入，行转列是数据可视化的前置步骤；
4. 灵活处理动态维度：通过动态SQL或数据库特性（如PostgreSQL的crosstab），可适配维度值动态变化的场景（如新增月份、区域）。

六、面试题

6.1 如何动态生成月份列？

问题：当revenue_data包含不确定的月份（如2023-Jan, 2024-Feb）时，如何自动生成列？

答案：

1. 应用层方案：用代码查询月份列表，动态拼接SQL

# Python伪代码
months = db.query("SELECT DISTINCT month FROM revenue_data")
columns = [f"SUM(CASE WHEN month='{m}' THEN revenue END) AS {m}_Revenue" 
           for m in months]
sql = f"SELECT id, {','.join(columns)} FROM revenue_data GROUP BY id"

1. 数据库层方案：使用原生PIVOT语法（SQL Server）或crosstab函数（PostgreSQL）

-- SQL Server动态PIVOT示例
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(month) 
                      FROM revenue_data 
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @query = 'SELECT id, ' + @cols + ' 
              FROM revenue_data 
              PIVOT (SUM(revenue) FOR month IN (' + @cols + ')) AS pvt';

EXEC sp_executesql @query;

6.2 如何处理多年度数据？

问题：表中有year列，需输出2023_Jan, 2024_Feb等列

答案：

在CASE条件中同时考虑年份和月份：

SELECT
    id,
    SUM(CASE WHEN year = 2023 AND month = 'Jan' THEN revenue END) AS "2023_Jan_Revenue",
    SUM(CASE WHEN year = 2023 AND month = 'Feb' THEN revenue END) AS "2023_Feb_Revenue",
    SUM(CASE WHEN year = 2024 AND month = 'Jan' THEN revenue END) AS "2024_Jan_Revenue",
    -- 其他年份月份...
FROM revenue_data
GROUP BY id;

6.3 性能优化实战

问题：当revenue_data有10亿行时，如何优化查询？

答案：

1. 分区表：按year/month分区

-- PostgreSQL示例
CREATE TABLE revenue_data (
    id INT,
    year INT,
    month CHAR(3),
    revenue DECIMAL(10,2)
) PARTITION BY RANGE (year);

CREATE TABLE revenue_data_2023 PARTITION OF revenue_data
    FOR VALUES FROM (2023) TO (2024);
CREATE TABLE revenue_data_2024 PARTITION OF revenue_data
    FOR VALUES FROM (2024) TO (2025);

1. 列式存储：使用Redshift/BigQuery等列式数据库
2. 预聚合：创建汇总表存储月度结果

-- 创建预聚合表
CREATE TABLE monthly_aggregated_data AS
SELECT
    id,
    year,
    month,
    SUM(revenue) AS total_revenue
FROM revenue_data
GROUP BY id, year, month;

-- 查询预聚合表
SELECT
    id,
    COALESCE(SUM(CASE WHEN year = 2023 AND month = 'Jan' THEN total_revenue END), 0) AS "2023_Jan_Revenue",
    -- 其他列...
FROM monthly_aggregated_data
GROUP BY id;

1. 并行处理：启用并行查询（如Oracle PARALLEL提示）
2. 向量化计算：使用GPU加速数据库

七、总结

行转列操作是SQL中一项重要的数据处理技术，其核心在于条件聚合和分组。在实际应用中，需要注意以下几点：

1. NULL值处理：始终使用COALESCE等函数处理NULL值，避免计算错误
2. 静态列与动态列：静态列方案仅适用于固定维度，动态列需通过编程或数据库特性实现
3. 性能优化：数据量较大时，需考虑索引优化、预聚合、分区表等技术
4. 跨数据库差异：不同数据库对行转列的支持方式存在差异，需了解目标数据库的特性

通过合理使用行转列技术，并结合索引优化和物化视图等手段，可以在亿级数据量下实现亚秒级响应，使其成为数据仓库和BI系统的核心技术。