Helt tilbage i 2012 skrev jeg et tip om at samle flere rækker til een. Min ide brugte left join til opgaven, og var dermed begrænset i antallet af rækker, den kunne samle. Jeg gjorde samtidig opmærksom på, at opgaven kunne løses rekursivt, men jeg havde ikke sat mig ind i, hvordan det kunne lade sig gøre. For nylig fik jeg så en opgave, hvor jeg fik brug for samme ide, men fik løst opgaven rekursivt. Jeg synes derfor, at jeg vil dele den rekursive løsning med dig.

Jeg vil bruge samme opgave som i det tidligere tip, så du kan se forskellen. For at repetere går opgaven ud på at vise een række pr. tabel i system-kataloget, hvor rækken indeholder både tabelnavnet og alle kolonnerne i den primære nøgle. Den rekursive løsning ser således ud:

WITH RECURSIVERESULT (CREATOR, TABLE, KEYSEQ, PRIMARY_KEY_COLUMNS) AS (
SELECT T.CREATOR, T.NAME, C.KEYSEQ, CAST(STRIP(C.NAME) AS VARCHAR(254))
FROM SYSIBM.SYSTABLES T, SYSIBM.SYSCOLUMNS C
WHERE T.CREATOR = 'SYSIBM'
  AND T.NAME LIKE 'SYS%'
  AND T.TYPE = 'T'
  AND T.KEYCOLUMNS > 0
  AND T.CREATOR = C.TBCREATOR
  AND T.NAME = C.TBNAME
  AND C.KEYSEQ = 1
UNION ALL
SELECT R.CREATOR, R.TABLE, C.KEYSEQ
     , R.PRIMARY_KEY_COLUMNS || ' ' || STRIP(C.NAME)
FROM RECURSIVERESULT R, SYSIBM.SYSCOLUMNS C
WHERE R.CREATOR = C.TBCREATOR
  AND R.TABLE = C.TBNAME
  AND R.KEYSEQ + 1 = C.KEYSEQ
)
SELECT TABLE, PRIMARY_KEY_COLUMNS
FROM RECURSIVERESULT R
WHERE KEYSEQ =
( SELECT MAX(KEYSEQ) FROM RECURSIVERESULT
  WHERE R.CREATOR = CREATOR
    AND R.TABLE = TABLE
)
ORDER BY TABLE

I forhold til det tidligere tip ser ovenstående SQL mere kompliceret ud. Til gengæld virker det uanset antallet af kolonner i den primære nøgle. Der er dog en begrænsning på 254 tegn i antallet af tegn i den sammensatte nøgle. Det kan justeres ved at rette på VARCHAR(254) til eksempelvis VARCHAR(1000) eller noget endnu større. I den endelige SELECT fra det fulde opsamlede rekursive resultat i RECURSIVERESULT er alle rækkerne for hver eneste rekursion gemt, men det er kun den række med alle de primære nøgler der er interessant, og den er kendetegnet ved at have den højeste værdi for KEYSEQ. Derfor indeholder WHERE betingelsen en sub-select, så kun rækker med MAX(KEYSEQ) pr. tabel indgår i det endelige resultat.

I dette eksempel er det ligetil at identificere den række i RECURSIVERESULT, hvor alle rækkerne er samlet til een række. Samtidigt er det via KEYSEQ let at bestemme den næste rekursion. Hvis du får en opgave, hvor du også ønsker at samle flere rækker til een række ved hjælp af rekursion, men du mangler en kolonne, der svarer til KEYSEQ, så kan du benytte dig af ROWNUMBER() OVER(PARTITION BY col1, col2, ...) til at generere din KEYSEQ. Og så skal du også lige lave en arbejdstabel med din genererede KEYSEQ. Ovenstående eksempel vil komme til at se således ud, hvis KEYSEQ ikke fandtes:

WITH WORKRESULT (CREATOR, TABLE, KEYSEQ, KEYCOL) AS (
SELECT T.CREATOR, T.NAME
       , ROWNUMBER() OVER(PARTITION BY T.CREATOR, T.NAME
                         )
       , CAST(STRIP(C.NAME) AS VARCHAR(254))
FROM SYSIBM.SYSTABLES T, SYSIBM.SYSCOLUMNS C
WHERE T.CREATOR = 'SYSIBM'
  AND T.NAME LIKE 'SYS%'
  AND T.TYPE = 'T'
  AND T.KEYCOLUMNS > 0
  AND T.CREATOR = C.TBCREATOR
  AND T.NAME = C.TBNAME
  AND C.KEYSEQ > 0
), RECURSIVERESULT (CREATOR, TABLE, KEYSEQ, PRIMARY_KEY_COLUMNS) AS (
SELECT CREATOR, TABLE, KEYSEQ, KEYCOL
FROM WORKRESULT
WHERE KEYSEQ = 1
UNION ALL
SELECT R.CREATOR, R.TABLE, W.KEYSEQ
     , R.PRIMARY_KEY_COLUMNS || ' ' || W.KEYCOL
FROM RECURSIVERESULT R, WORKRESULT W
WHERE R.CREATOR = W.CREATOR
  AND R.TABLE = W.TABLE
  AND R.KEYSEQ + 1 = W.KEYSEQ
)
SELECT TABLE, PRIMARY_KEY_COLUMNS
FROM RECURSIVERESULT R
WHERE KEYSEQ =
( SELECT MAX(KEYSEQ) FROM RECURSIVERESULT
  WHERE R.CREATOR = CREATOR
    AND R.TABLE = TABLE
)
ORDER BY TABLE

Dette SQL statement giver næsten det samme resultat som det første. Eneste forskel er, at rækkefølgen af kolonnerne i PRIMARY_KEY_COLUMNS sandsynligvis vil være en anden, da jeg har udeladt kolonnen, der angiver den korrekte rækkefølge. Det kan jo sagtens tænkes, at den opgave, du skal løse med at samle flere række til een ikke har information om en indbyrdes rækkefølge mellem rækkerne, der skal samles. Du kan dog få det samme resultat som i det første eksempel ved at tilføje ORDER BY C.KEYSEQ indeni selve OVER parentesen efter PARTITION udtrykket.