DB2 - Multiple rows to 1 row with multiple columns

Question

I know I've done this before, but I can't figure this out right now. I get multiple rows in my query but I want one row per Id2.

Tried a lot of variants and googled. Found some promising examples with row number but couldn't make it work.

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

I have all the Id2:s in a list.

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

Gives

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

I wan't this result:

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

Answer 1

You can use window function ROW_NUMBER() to assign a unique sequential integer to each row within partitions defined by id2, then apply the conditional aggregation :

SELECT Id2, MAX(CASE WHEN rn = 1 then Value END) as Value1,
            MAX(CASE WHEN rn > 1 then Value END) as Value2
FROM (
  SELECT Id2, Value, ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY Value) AS rn
  FROM Table1 a
  JOIN Table2 b ON a.Id1 = b.id1
  WHERE a.Id2 in ('T100100','T761232','T441122')
) as s
GROUP BY Id2;

Demo here