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
