如何使用PIVOT同时将line_name与org_code多行转多列？

方法一：条件聚合（推荐，简洁高效） #

这是最直接的实现方式，通过CASE WHEN结合聚合函数（如MAX，假设每个op_id + line_type组合唯一），一次性生成每个line_type对应的line_name和org_code列。

示例SQL：

SELECT
    op_id,
    -- 针对每个line_type生成对应的line_name和org_code列
    MAX(CASE WHEN line_type = 'A' THEN line_name END) AS A_line_name,
    MAX(CASE WHEN line_type = 'A' THEN org_code END) AS A_org_code,
    MAX(CASE WHEN line_type = 'B' THEN line_name END) AS B_line_name,
    MAX(CASE WHEN line_type = 'B' THEN org_code END) AS B_org_code,
    MAX(CASE WHEN line_type = 'C' THEN line_name END) AS C_line_name,
    MAX(CASE WHEN line_type = 'C' THEN org_code END) AS C_org_code
FROM your_table
GROUP BY op_id;

如果你的line_type取值不固定（无法提前枚举），可以用动态SQL自动生成列：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 自动拼接所有line_type对应的列逻辑
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN line_type = ''', line_type, ''' THEN line_name END) AS ', QUOTENAME(line_type + '_line_name'), ',',
        'MAX(CASE WHEN line_type = ''', line_type, ''' THEN org_code END) AS ', QUOTENAME(line_type + '_org_code')
    ),
    ','
)
FROM (SELECT DISTINCT line_type FROM your_table) t;

-- 构造并执行完整SQL
SET @sql = CONCAT('
SELECT op_id, ', @cols, '
FROM your_table
GROUP BY op_id;
');

EXEC sp_executesql @sql;

方法二：两次PIVOT关联（适合坚持用PIVOT的场景） #

由于PIVOT一次只能针对一个聚合列，你可以分别对line_name和org_code做PIVOT，再通过op_id关联结果：

示例SQL：

WITH pivot_line_name AS (
    SELECT op_id, A AS A_line_name, B AS B_line_name, C AS C_line_name
    FROM (
        SELECT op_id, line_type, line_name
        FROM your_table
    ) src
    PIVOT (
        MAX(line_name) FOR line_type IN ('A' AS A, 'B' AS B, 'C' AS C)
    ) pvt
),
pivot_org_code AS (
    SELECT op_id, A AS A_org_code, B AS B_org_code, C AS C_org_code
    FROM (
        SELECT op_id, line_type, org_code
        FROM your_table
    ) src
    PIVOT (
        MAX(org_code) FOR line_type IN ('A' AS A, 'B' AS B, 'C' AS C)
    ) pvt
)
SELECT
    pn.op_id,
    pn.A_line_name,
    po.A_org_code,
    pn.B_line_name,
    po.B_org_code,
    pn.C_line_name,
    po.C_org_code
FROM pivot_line_name pn
INNER JOIN pivot_org_code po ON pn.op_id = po.op_id;

选择建议 #

• 优先用条件聚合：代码更简洁，性能更优，1000行数据完全无压力。
• 如果业务要求必须用PIVOT，再考虑两次PIVOT关联的方式。

内容的提问来源于stack exchange，提问作者lolster