Oracle SQL：如何将同表中子数据按条件展示为单行多列？

数据表结构与示例数据 #

表结构 #

Columns = Id, parent_id, System, error, date_time, is_subsystem

（注：原数据示例包含is_subsystem字段，补充至表结构中）

数据示例 #

Id = 1, parent_id = 1, System = null, error = null, date_time= sysdate, is_subsystem= N
Id = 2, parent_id = 1, System = A, error = null, date_time= sysdate, is_subsystem= Y  
Id = 3, parent_id = 1, System = B, error = null, date_time= sysdate, is_subsystem= Y
Id = 4, parent_id = 1, System = C, error = null, date_time= sysdate, is_subsystem= Y
Id = 5, parent_id = 1, System = D, error = null, date_time= sysdate, is_subsystem= Y

需求 #

传入ID=1时，将所有parent_id匹配该ID的子数据（is_subsystem=Y）的System字段展示在同一行的多列中，期望结果如下：

当前SQL的问题 #

原SQL存在两处问题：

1. 关联条件错误：应为T2.parent_id = T1.Id，而非T2.id = T1.paren_id（存在拼写错误paren_id，正确为parent_id）
2. 即便修正条件，也仅能返回多行单条数据，无法实现同一行多列展示

解决方案 #

要实现子数据从行转列，可根据子系统数量是否固定选择不同方案：

方案1：固定子系统数量（已知最多列数） #

通过给子数据添加行号，结合条件聚合实现行列转换：

WITH child_data AS (
    SELECT 
        parent_id,
        System,
        -- 按ID排序给每个子数据分配行号
        ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY Id) AS rn
    FROM My_Table
    WHERE is_subsystem = 'Y'
)
SELECT 
    t1.Id,
    MAX(CASE WHEN cd.rn = 1 THEN cd.System END) AS System_of_Child_1,
    MAX(CASE WHEN cd.rn = 2 THEN cd.System END) AS System_of_Child_2,
    MAX(CASE WHEN cd.rn = 3 THEN cd.System END) AS System_of_Child_3,
    MAX(CASE WHEN cd.rn = 4 THEN cd.System END) AS System_of_Child_4
FROM My_Table t1
LEFT JOIN child_data cd ON t1.Id = cd.parent_id
WHERE t1.Id = 1
GROUP BY t1.Id;

该SQL会按子数据ID顺序，将System值依次放入对应列中，无数据的列显示NULL。

方案2：动态子系统数量（列数不固定） #

如果子系统数量不确定，可使用动态SQL自动生成对应列数（以Oracle为例）：

DECLARE
    cols_str VARCHAR2(2000);
BEGIN
    -- 动态生成列的聚合语句
    SELECT LISTAGG(
        'MAX(CASE WHEN rn = ' || rn || ' THEN System END) AS System_of_Child_' || rn,
        ', '
    ) INTO cols_str
    FROM (
        SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY Id) AS rn
        FROM My_Table
        WHERE parent_id = 1 AND is_subsystem = 'Y'
    );

    -- 执行动态拼接的SQL
    EXECUTE IMMEDIATE '
        WITH child_data AS (
            SELECT 
                parent_id,
                System,
                ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY Id) AS rn
            FROM My_Table
            WHERE parent_id = 1 AND is_subsystem = ''Y''
        )
        SELECT t1.Id, ' || cols_str || '
        FROM My_Table t1
        LEFT JOIN child_data cd ON t1.Id = cd.parent_id
        WHERE t1.Id = 1
        GROUP BY t1.Id';
END;
/

该方案会根据parent_id=1下的子数据实际数量，自动生成对应数量的列。

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