Spojení dvou tabulek včetně sloučení více řádek do jednoho sloupce v SQL pomocí FOR XML PATH

05.09.2016, Vladimír Klaus, navštíveno 7073x

Pro jeden projekt jsem potřeboval spojit dvě tabulky, přesněji řečeno vzít tabulku s hlavními daty a k tomu připojit detailní data. Každému řádku hlavní tabulky odpovídalo více řádek s detaily, takže klasický JOIN zde nepomůže, protože by vznikla spousta řádek a já jsem potřeboval zachovat jen počet řádek odpovídající hlavní tabulce.

Lze si to představit třeba tak, že máte tabulku produktů a další tabulku s fotografiemi produktů. Já jsem potřeboval tabulku s jednotlivými produkty, kde bych v dalším sloupci měl seskládané všechny obrázky.

Pomoci zde může klauzule FOR XML. Ta, zjednodušeně řečeno, bere data z tabulky a vrací tomu odpovídající XML. Řada věcí se dá ovlivnit parametrem, který následuje, my ale (bez dalšího vysvětlování neb je to problematika opravdu rozsáhlá) použijeme PATH.

SELECT Soubor FROM Fotogalerie
FOR XML PATH

Výsledkem je jedna "buňka", jeden údaj s XML, který, když si ho naformátujeme, bude vypadat třeba takto:

Možná si říkáte, že to je sice pěkné, ale jak to pomůže? Ještě pár kroků a uvidíte. Tím první krokem je použití PATH('').

SELECT Soubor FROM Fotogalerie
FOR XML PATH ('')

Výsledek je nyní mnohem jednodušší:

Další úprava bude ještě zajímavější, resp. její výsledek

SELECT ', ' + Soubor FROM Fotogalerie
FOR XML PATH ('')

Máme už vlastně jen sadu stringů, oddělených čárkami a XML tagy jsou pryč.

Teď se ještě zbavíme té čárky na začátku a je téměř hotovo. K tomu slouží funkce STUFF, která v zadaném textu nahradí část (zadanou počáteční a koncovou pozicí) jiným textem.

SELECT STUFF (
    (SELECT (', ' + Soubor) FROM Fotogalerie
    FOR XML PATH ('')), 1, 2, ''
)

V našem případě tedy nahradíme první dva znaky prázdným řetězcem, čímž se zbavíme té čárky a mezery. Výsledkem je tedy konečně zcela čistý seznam obrázků.

Nyní zbývá tento princip zabudovat do spojení dvou tabulek. V podstatě jde o dva vnořené SELECTy, které jsou propojené přes ID produktu. Vnitřní SELECT je tak postupně vykonán pro všechny shora dodané p.ID a vrací pro každý produkt jen jednu hodnotu ve sloupci SadaObrazku.

SELECT 
    Nazev,
    STUFF(
	(SELECT ', ' + f.Soubor	FROM Fotogalerie f
        WHERE f.IdProduktu = p.ID
        FOR XML PATH ('')
	), 1, 2, '') AS SadaObrazku
FROM Produkty p

Výsledek je parádní a je tím pádem velmi snadné pracovat z jedné "tabulky" se všemi produkty a všemi obrázky jednotlivých produktů.

A ještě malé doplnění na závěr. V případě, že by vám stačil třeba jen jeden obrázek pro každý produkt, můžete použít stejný princip, jen bez XML obezličky. Samozřejmostí pak je, že vnitřní SELECT musí vracet jen jeden řádek (třeba pomocí TOP 1), protože jinak by SQL nešlo vykonat.

SELECT Nazev,
    (
        SELECT TOP 1 Soubor FROM Fotogalerie f
        WHERE f.IdProduktu = p.ID
    )
FROM Produkty p

V případě SQL Serveru 2017 nebo novějšího je k dispozici úžasná funkce STRING_AGG, která to vše udělá jednoduše a přehledně.

SELECT 
    OrderNumber, 
    COUNT(ID) AS CountID,
    STRING_AGG(Code, ', ') AS Codes
FROM 
    SHIPMENTShipments
WHERE 
    JeAktualni = 1
GROUP BY 
    OrderNumber
HAVING 
    COUNT(ID) >= 2