DB2技术问询：如何按Id2将多行数据合并为一行多列

此前实现过将多行数据按Id2维度合并为单行的需求，但现在无法复现。当前关联查询返回多行结果，需要将同一Id2对应的多个Value转为单行的多列（Value1、Value2）。尝试过含row_number的写法但未成功。

现有表结构及数据：

Table1 #

Id1            Id2
1234500        T100100
1234501        T100100
1423400        T761232
1456100        T441122
1456101        T441122

Table2 #

Id1            Value
1234500        1015
1234501        1080
1423400        1080
1456100        1044
1456101        1077

执行关联查询：

SELECT Id2, Value
FROM Table1 a
JOIN Table2 b
  ON a.Id1 = b.id1
WHERE a.Id2 in ('T100100','T761232','T441122')

得到结果：

Id2          Value
T100100        1015
T100100        1080
T761232        1080
T441122        1044
T441122        1077

期望结果：

Id2            Value1    Value2
T100100        1015      1080
T761232        1080      null or space or 0
T441122        1044      1077

可以通过行号标记+条件聚合或者PIVOT函数实现需求，以下是具体写法：

方法1：行号标记+条件聚合 #

先使用row_number()为每个Id2分组内的Value分配序号，再用聚合函数按Id2分组，提取对应序号的Value：

SELECT
    Id2,
    MAX(CASE WHEN rn = 1 THEN Value END) AS Value1,
    MAX(CASE WHEN rn = 2 THEN Value END) AS Value2
FROM (
    SELECT
        a.Id2,
        b.Value,
        ROW_NUMBER() OVER(PARTITION BY a.Id2 ORDER BY a.Id1) AS rn
    FROM Table1 a
    JOIN Table2 b ON a.Id1 = b.Id1
    WHERE a.Id2 IN ('T100100','T761232','T441122')
) t
GROUP BY Id2;

说明：ORDER BY a.Id1保证序号对应Id1的顺序，若不需要特定顺序可去掉或调整排序字段；如果需要将空值转为0，可把MAX(CASE...)改为ISNULL(MAX(CASE...), 0)。

方法2：使用PIVOT函数（适用于SQL Server等支持该函数的数据库） #

SELECT Id2, [1] AS Value1, [2] AS Value2
FROM (
    SELECT
        a.Id2,
        b.Value,
        ROW_NUMBER() OVER(PARTITION BY a.Id2 ORDER BY a.Id1) AS rn
    FROM Table1 a
    JOIN Table2 b ON a.Id1 = b.Id1
    WHERE a.Id2 IN ('T100100','T761232','T441122')
) t
PIVOT (
    MAX(Value) FOR rn IN ([1], [2])
) p;

说明：同样通过row_number()生成序号，再用PIVOT将行转列，空值会显示为NULL，如需转为0可使用ISNULL([1], 0)替代[1]。

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