Versie SQL Server 2005 en hoger

Het gebeurt in een database regelmatig dat waarden moeten worden samengevoegd uit verschillende rijen. Bijvoorbeeld bij het maken van een kommagescheiden lijst van alle landen in een database. Tot voor kort gebruikte ik daar altijd COALESCE voor. Deze functie retourneert de eerste niet lege waarde van een reeks argumenten en COALESCE kan recursief in een query worden gebruikt als ook de mogelijkheid bestaat om variabelen in te zetten (maar dat is niet altijd mogelijk).

Voorbeeldtabel:

Ik heb een tabel met alle Europese landen  en een boolean waarde die aangeeft of ze momenteel (mei 2012) lid zijn van de Europese Unie.

Land	Lid
België	1
Bulgarije	1
Cyprus	1
Tsjechië	1
Zweden	1
Verenigd Koninkrijk	1
Kroatië	0
Albanië	0
Andorra	0
Macedonië	0
IJsland	0
Montenegro	0
Servië	0
Turkije	0
…

Om nu met Coalesce een lijst van alle landen te maken die lid zijn gebruik:

DECLARE @EuroUnie NVARCHAR(MAX)
SELECT  @EuroUnie = COALESCE(@EuroUnie + ', ', '') + Land
FROM  tblLanden
WHERE  lid = 1
order by  Land

De uitkomst is:

België, Bulgarije, Cyprus, Denemarken, Duitsland, Estland, Finland, 
Frankrijk, Griekenland, Hongarije, Ierland, Italië, Letland, Litouwen, 
Luxemburg, Malta, Nederland, Oostenrijk, Polen, Portugal, Roemenie, 
Slovenië, Slowakije, Tsjechië, Verenigd Koninkrijk, Zweden

Ooit las ik ergens dat dit ook kan met XML, maar ik heb er eigenlijk nooit echt naar gekeken. Waarom XML inzetten als je geen XML nodig hebt? Tot ik op een dag een query nodig had die geen variabele kon gebruiken. Ik zocht de XML-variant op en tot mijn grote vreugde is die velen malen sneller. 23.000 records samenvoegen in een fractie van de tijd die COALESCE nodig heeft.

De code is wel wat lastiger te lezen, dus hierbij een uitleg stap voor stap.

Eerst de basisquery. Deze heeft weinig uitleg nodig.

select Land from tblLanden where lid=1 order by Land

Deze kan in XML worden gevangen door een for xml path toe te voegen

select Land from tblLanden where lid=1 order by Land for xml path('')

Doordat ik geen parameters specificeer bij path, zal SQL Server de naam van het veld gebruiken als XML naam:

<Land>België</Land><Land>Bulgarije</Land><Land>Cyprus</Land>
<Land>Denemarken</Land><Land>Duitsland</Land><Land>Estland</Land>
<Land>Finland</Land><Land>Frankrijk</Land><Land>Griekenland</Land>
<Land>Hongarije</Land><Land>Ierland</Land><Land>Italië</Land>
<Land>Letland</Land><Land>Litouwen</Land><Land>Luxemburg</Land>
<Land>Malta</Land><Land>Nederland</Land><Land>Oostenrijk</Land>
<Land>Polen</Land><Land>Portugal</Land><Land>Roemenië</Land>
<Land>Slovenië</Land><Land>Slowakije</Land><Land>Tsjechië</Land>
<Land>Verenigd Koninkrijk</Land><Land>Zweden</Land>

Maar als we het veld nu specifiek naar text() hernoemen, zal SQL server de xml names weglaten.

select  Land as [text()]  from tblLanden where lid=1 
order by Land for xml path('')

BelgiëBulgarijeCyprusDenemarkenDuitslandEstlandFinlandFrankrijk
GriekenlandHongarijeIerlandItaliëLetlandLitouwenLuxemburgMalta
NederlandOostenrijkPolenPortugalRoemeniëSloveniëSlowakijeTsjechië
Verenigd KoninkrijkZweden

Even wat spaties en komma’s toevoegen en we zijn er bijna.

select  ', ' + Land as [text()]  from tblLanden where lid=1 
order by Land for xml path('')

 , België, Bulgarije, Cyprus, Denemarken, Duitsland, Estland, Finland, 
Frankrijk, Griekenland, Hongarije, Ierland, Italië, Letland, Litouwen, 
Luxemburg, Malta, Nederland, Oostenrijk, Polen, Portugal, Roemenië, 
Slovenië, Slowakije, Tsjechië, Verenigd Koninkrijk, Zweden

De komma die ontstaat voor het eerste land, is niet moeilijk weg te werken. Eerst ga ik een ander issue oplossen. For XML geeft een XML datatype terug in plaats van een platte string. Dit kan opgelost worden met de functie Stuff. Deze functie plaatst een string in een andere string op een bepaalde positie.

select STUFF((select  ', ' + Land as [text()]  from tblLanden 
where lid=1 order by Land for xml path('')),1,1,'')

Met Stuff kan in het XML-resultaat het eerste karakter (de overtollige komma) worden vervangen met een lege string. Het resultaat is een string.

Deze techniek heeft in mijn database met miljoenen records keer op keer laten zien dat het een stuk sneller is dan COALESCE.