I'm trying to build a query. I have a parent table, which has unique row for each account. Child table will have multiple rows for every single row on the parent table. Child table and parent table by the DB2 generated row ID.

Parent table.

ROW_ID      DATE        ACCT
1234563   2024-01-31   333555
1234564   2024-01-31   333666
1234565   2024-01-31   333777

Child table

ROW_ID     CRD_DB_YN    AMT   CODE
1234563         D       100     A
1234563         D       200     C
1234563         C       300     D
1234564         D       100     X
1234565         D       250     A
1234565         C       300     C

I want output from my query as below.

ROW_ID   ACCT     CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE
1234563   333555        D      100      A      D        200      C      C      300       D
1234564   333666        D      100      X 
1234565   333777        D      250      A      C        300      C

I'm trying to build a query. I have a parent table, which has unique row for each account. Child table will have multiple rows for every single row on the parent table. Child table and parent table by the DB2 generated row ID.

Parent table.

ROW_ID      DATE        ACCT
1234563   2024-01-31   333555
1234564   2024-01-31   333666
1234565   2024-01-31   333777

Child table

ROW_ID     CRD_DB_YN    AMT   CODE
1234563         D       100     A
1234563         D       200     C
1234563         C       300     D
1234564         D       100     X
1234565         D       250     A
1234565         C       300     C

I want output from my query as below.

ROW_ID   ACCT     CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE  CRD_DB_YN   AMT    CODE
1234563   333555        D      100      A      D        200      C      C      300       D
1234564   333666        D      100      X 
1234565   333777        D      250      A      C        300      C

• sql
• db2

• 1 You want a "pivot". Do you know the number of maximum columns in advance? If not, that would be a dynamic pivot, that you can do with a stored procedure; or you can do it in your app instead of doing it in the database. – The Impaler Commented Jan 31 at 16:52

1 Answer 1

EDIT : In case the child level is not fixed, you can generate the above query using dynamic sql => Fiddle

In case your child level is fixed, you can first rank the children based on row_id and then based on the ranks the columns CRD_DB_YN , AMT and Code are repeated for each rank.

WITH rank_child AS (
  SELECT
    p.ROW_ID AS P_ROW_ID,
    p.ACCT,
    c.ROW_ID AS C_ROW_ID,
    c.CRD_DB_YN,
    c.AMT,
    c.CODE,
    ROW_NUMBER() OVER (PARTITION BY c.ROW_ID ORDER BY c.ROW_ID) AS rn
  FROM
    parent p
  INNER JOIN
    child c
  ON
    p.ROW_ID = c.ROW_ID
)
SELECT
  rc.P_ROW_ID AS ROW_ID,
  rc.ACCT,
  MAX(CASE WHEN rc.rn = 1 THEN rc.CRD_DB_YN END) AS CRD_DB_YN1,
  MAX(CASE WHEN rc.rn = 1 THEN rc.AMT END) AS AMT1,
  MAX(CASE WHEN rc.rn = 1 THEN rc.CODE END) AS CODE1,
  MAX(CASE WHEN rc.rn = 2 THEN rc.CRD_DB_YN END) AS CRD_DB_YN2,
  MAX(CASE WHEN rc.rn = 2 THEN rc.AMT END) AS AMT2,
  MAX(CASE WHEN rc.rn = 2 THEN rc.CODE END) AS CODE2,
  MAX(CASE WHEN rc.rn = 3 THEN rc.CRD_DB_YN END) AS CRD_DB_YN3,
  MAX(CASE WHEN rc.rn = 3 THEN rc.AMT END) AS AMT3,
  MAX(CASE WHEN rc.rn = 3 THEN rc.CODE END) AS CODE3
FROM
  rank_child rc
GROUP BY
  rc.P_ROW_ID, rc.ACCT
ORDER BY
  rc.P_ROW_ID;

Output

Fiddle