Listede kolonnedata i Oracle 11g Release 2

En hyppig tilbagevendende problemstilling når man laver databaseudtræk til ”flade filer” i relationelle databaser som f.eks. Oracle, er at få 3- dimensionelle data til at blive 2-dimensionelle, så de passer ind i et filudtræk.

Et eksempel kan være: Giv mig de postnumre med tilhørende byer som vi har solgt til i denne uge, hvor mange varer vi har solgt, og også gerne kundenumrene på dem vi har solgt til.

Et typisk separeret udtræk vil så være med gentagne postnumre som f.eks.:

Postnr; By; Antal; Kundenr
8000; Århus C; 3; 1234
8000; Århus C; 2; 1275
8000; Århus C; 1; 1289
8200; Århus N; 2; 1237
8200; Århus N; 1; 1285

Det svar man typisk får af den der ønsker udtrækket er så: Nej, jeg vil jo kun have postnumrene én gang hver, sammen med summen af varer, for det er det vigtigste. Kundenumre er kun til info, så kan du ikke bare liste dem efter hinanden.

Dette er SQL ikke særligt stærkt til, da det normalt altid arbejder med specifikke kolonner til specifikke data. Man har indtil nu i Oracle typisk selv skulle lave en funktion der kunne dette, eller finde en udvikler der havde lavet en mere generel funktion. En anden mulighed fra Oracle 10g og frem, var at bruge den forholdsvis komplekse MODEL clause til at opnå det samme.

Men fra og med Oracle 11g Release 2 er der kommet en tilføjelse til Oracles analytiske funktioner, som gør dette ”native” uden yderligere tilføjelser. Den hedder LISTAGG.

Vi forestiller os at vores tabel ser således ud:
vores_test_tabel
postnr                number(4)
postby               varchar2(50)
kundenr            number(10)
antal                   number(5)

Dette er naturligvis et fortænkt eksempel, da disse data normalt vil være normaliseret i mindst 2 tabeller, men eksemplet virker på præcis samme måde ved joins.

Vi kan så udtrække de ønskede data med:

SELECT postnr
,postby
,LISTAGG(kundenr, ‘,’) WITHIN GROUP AS kundenumre
FROM vores_test_tabel
GROUP BY postnr,postby
ORDER BY postnr

Dette giver os resultatet:

POSTNR            POSTBY             KUNDENUMRE
8000                    Århus C             1234,1275,1289
8200                    Århus N             1237,1285

Det vi beder om med LISTAGG funktionen er altså at få alle kundenumrene listet i sorteret rækkefølge med et komma imellem hver, og få det opdelt på vores group by, som er postnr og postby.

Det er ikke noget problem at udvide dette med traditionelle aggregeringsfunktioner som count eller sum, hvis disse optællinger af antal ønskes:

SELECT postnr
,postby
,count(antal) antal_ordrer
,sum(antal)   antal_varer
,LISTAGG(kundenr, ‘,’) WITHIN GROUP AS kundenumre
FROM vores_test_tabel
GROUP BY postnr,postby
ORDER BY postnr

Dette giver resultatet:

POSTNR            POSTBY             ANTAL_ORDRER                         ANTAL_VARER                            KUNDENUMRE
8000                    Århus C             3                                                        6                                                        1234,1275,1289
8200                    Århus N             2                                                        3                                                        1237,1285

En lille udvidelse til dette kan være at udnytte, at en af de gængse filformater til sådanne data er .csv, som på de fleste PC’ere automatisk vil åbne sig i MS EXCEL, og derudover i Danmark er semikolonsepareret som default.

Dvs. at hvis vi gemmer outputtet ovenfor som semikolon separeret fil, f.eks. med ”spool” direkte i SQL*Plus, vil det i Excel derefter se således ud:

8000 Århus C 3 6 1234,1275,1289
8200 Århus N 2 3 1237,1285

Men hvis modtageren ønsker at kundenumre i stedet skal optage så mange celler som der nu er brug for, kan vi bruge vores viden om, at.csv filer er semikolonseparerede til få LISTAGG til at generere kolonner.

Så vi erstatter LISTAGG(kundenr, ‘,’) med LISTAGG(kundenr, ‘;’).

Når resultatet gemmes som .csv, og åbnes automatisk i Excel, vil resultatet så se således ud:

8000 Århus C 3 6 1234 1275 1289
8200 Århus N 2 3 1237 1285

Eftersom LISTAGG er en af de analytiske funktioner i Oracle 11g Release2, kan den bruges i sammenhæng med de øvrige opdelinger og nedbrydningsmuligheder som de analytiske funktioner indeholder. Man kan læse mere om dette f.eks. her: https://www.oracle-developer.net/display.php?id=515

I have been working as a system developer, data modeler and DBA on the Oracle platform using SQL, Pl/SQL, Forms, Reports, JSP, HTML and Javascript since 1996, and have been involved in both client/server and web-based projects for 25+ different customers. I'm normally known as a problem solver