如何将JOIN查询结果中同ID的多行数据转为横向列展示？

要将单个ID对应多个ticket的纵向查询结果转为横向展示，核心是**行列转置（Pivot）**操作，不同数据库的实现语法略有差异，以下是主流场景的具体解决办法：

1. 固定列场景（已知最大关联ticket数量） #

如果能确定每个ID最多关联的ticket数量（比如最多4个），用条件聚合的方式兼容性最好，后续扩展关联列也很方便：

通用SQL（适配多数数据库） #

SELECT
    id.id,
    MAX(CASE WHEN rn = 1 THEN ticket.ticket END) AS Column_B,
    MAX(CASE WHEN rn = 2 THEN ticket.ticket END) AS Col_c,
    MAX(CASE WHEN rn = 3 THEN ticket.ticket END) AS Col_D,
    MAX(CASE WHEN rn = 4 THEN ticket.ticket END) AS Col_N
FROM (
    SELECT 
        id.id, 
        ticket.ticket,
        -- 给每个ID下的ticket按顺序编号
        ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
    FROM tabla.id 
    JOIN tabla.ticket ON id.sym = ticket.sym
) AS sub
GROUP BY id.id;

• 原理：先用ROW_NUMBER()给每个ID下的ticket编号，再通过CASE+聚合函数将不同编号的ticket映射到对应列，无匹配值的位置自动填充NULL。
• 扩展关联列：直接在子查询中新增字段，再用CASE+聚合函数的格式添加对应列即可。

2. 动态列场景（未知最大关联ticket数量） #

如果无法确定每个ID关联的ticket数量，需要动态生成列，不同数据库的实现方式如下：

MySQL/MariaDB #

借助存储过程动态拼接SQL：

DELIMITER //
CREATE PROCEDURE pivot_tickets()
BEGIN
    -- 生成动态列的SQL片段
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN ticket END) AS `Column_', rn, '`')
    ) INTO @cols
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
        FROM tabla.id 
        JOIN tabla.ticket ON id.sym = ticket.sym
    ) AS sub;

    -- 拼接完整SQL并执行
    SET @sql = CONCAT('
        SELECT id.id, ', @cols, '
        FROM (
            SELECT 
                id.id, 
                ticket.ticket,
                ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
            FROM tabla.id 
            JOIN tabla.ticket ON id.sym = ticket.sym
        ) AS sub
        GROUP BY id.id
    ');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL pivot_tickets();

PostgreSQL #

使用crosstab函数（需先启用tablefunc扩展）：

-- 启用扩展（仅首次执行）
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
    -- 源数据：按ID和ticket编号排序
    'SELECT id.id, rn, ticket.ticket
     FROM (
         SELECT 
             id.id, 
             ticket.ticket,
             ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
         FROM tabla.id 
         JOIN tabla.ticket ON id.sym = ticket.sym
     ) AS sub
     ORDER BY 1, 2',
    -- 定义列的编号范围
    'SELECT DISTINCT rn FROM (
         SELECT ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
         FROM tabla.id 
         JOIN tabla.ticket ON id.sym = ticket.sym
     ) AS sub ORDER BY rn'
) AS ct(id INT, Column_B VARCHAR, Col_c VARCHAR, Col_D VARCHAR, Col_N VARCHAR);

• 注意：如果实际最大编号超过定义的列数，需手动调整ct后的列定义，或用动态SQL生成列名。

SQL Server #

使用PIVOT关键字：

-- 固定列场景
SELECT id, [1] AS Column_B, [2] AS Col_c, [3] AS Col_D, [4] AS Col_N
FROM (
    SELECT 
        id.id, 
        ticket.ticket,
        ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
    FROM tabla.id 
    JOIN tabla.ticket ON id.sym = ticket.sym
) AS sub
PIVOT (
    MAX(ticket) FOR rn IN ([1], [2], [3], [4])
) AS pvt;

-- 动态列场景
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(CONCAT('[', rn, ']'), ',')
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
    FROM tabla.id 
    JOIN tabla.ticket ON id.sym = ticket.sym
) AS sub;

-- 拼接SQL并替换列别名
SET @sql = CONCAT('
    SELECT id, 
           REPLACE(', @cols, ', ''[1]'', ''[1] AS Column_B''),
           REPLACE(', @cols, ', ''[2]'', ''[2] AS Col_c''),
           REPLACE(', @cols, ', ''[3]'', ''[3] AS Col_D''),
           REPLACE(', @cols, ', ''[4]'', ''[4] AS Col_N'')
    FROM (
        SELECT 
            id.id, 
            ticket.ticket,
            ROW_NUMBER() OVER (PARTITION BY id.id ORDER BY ticket.ticket) AS rn
        FROM tabla.id 
        JOIN tabla.ticket ON id.sym = ticket.sym
    ) AS sub
    PIVOT (
        MAX(ticket) FOR rn IN (', @cols, ')
    ) AS pvt
');

EXEC sp_executesql @sql;

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