Spis treści:

Kategoria:AgregacjeSQL Server


Tworzenie listy rozdzielonej przecinkiem z danych jednej kolumny w SQL Server

Problem wydaje się dość powszechny. Przypuśćmy, że w jednej tabeli mamy listę urządzeń i chcemy zwrócić je w jednym rekordzie, oddzielając każdą wartość dowolnym separatorem. Taką operację niektórzy nazywają konkatenacją. Ci, którzy znają .NET z pewnością spotkali się z funkcją String.Join. To właśnie taką fukcję chcemy zasymulować. Skrypt potrzebny do wykonania takiej operacji będzie wyglądał następująco:

CREATE TABLE Hardware
(
Name nvarchar(16)
)
INSERT INTO Hardware VALUES (N'Twardy dysk')
INSERT INTO Hardware VALUES (N'Procesor')
INSERT INTO Hardware VALUES (N'Płyta główna')
INSERT INTO Hardware VALUES (N'Karta graficzna')

DECLARE @lista nvarchar(MAX)
SELECT @lista = COALESCE(@lista+',' ,'') + Name
FROM Hardware
SELECT @lista

Wynikiem działania powyższego skryptu będzie taki oto rezultat:

Twardy dysk,Procesor,Płyta główna,Karta graficzna

Zaprezentowany przykład pokazuje, w jaki sposób odzielić dane z jednej kolumny. Co wtedy, gdy zapytanie jest bardziej skomplikowane?

Agregacja kolumny do jednego elementu w trudniejszych zapytaniach

Przypuśćmy, że mamy tabelę, która zawiera dwie kolumny. Pierwsza zawiera identyfikator magazynu, druga natomiast towar, który w tym magazynie się znajduje. Dane w tabeli dla potrzeb naszego przykładu niech wyglądają następująco:

IDName
1borsuki
2kuny
1konie
2surykatki
3marchewki
4bakłażany
1psy

Naszym celem jest napisanie skryptu, który zwróci wynik w takiej postaci:

IDNames
1borsuki,konie,psy
2kuny,surykatki
3marchewki
4bakłażany

Sprawa komplikuje się dlatego, że nie da się jak w poprzednim przykładzie zdefiniować zmiennej i uzupełnić ją prostym zapytaniem. Tym razem musi to być wiele zmiennych (jeżeli znamy ilość rekordów) lub tabela. Jedno z rozwiązań, które stosuję, nie jest już prostym zapytaniem. To już dłuższa procedura wykorzystująca kursory.

Przygotujmy sobie zatem tabelę, która będzie odpytywana:

CREATE TABLE Boxes
(
ID int,
Name nvarchar(16)
)

--uzupełnienie tabeli danymi
INSERT INTO Boxes VALUES (1,'borsuki')
INSERT INTO Boxes VALUES (2,'kuny')
INSERT INTO Boxes VALUES (1,'konie')
INSERT INTO Boxes VALUES (2,'surykatki')
INSERT INTO Boxes VALUES (3,'marchewki')
INSERT INTO Boxes VALUES (4,'bakłażany')
INSERT INTO Boxes VALUES (1,'psy')

Skrypt właściwy łączący dane przecinkiem

Pora na zaprezentowanie kodu. Zanim wyjaśnię zasadę działania przyjrzyjmy się przykładowemu rozwiązaniu:

CREATE TABLE #tmp
(
ID int,
Names nvarchar(MAX)
)

DECLARE Cur CURSOR FOR
    SELECT ID, Name
    FROM Boxes ORDER BY ID,Name
    
DECLARE @id int
DECLARE @name nvarchar(16)
DECLARE @val nvarchar(MAX)
OPEN Cur

FETCH NEXT FROM Cur INTO @id,@name
DECLARE @lastID int = @id
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @id<>@lastID
    BEGIN
        INSERT INTO #tmp VALUES (@lastID, @val)
        SET @val = NULL
        SET @lastID = @id
    END
    SET @val = COALESCE(@val+',','')+@name
    FETCH NEXT FROM Cur INTO @id,@name
END
INSERT INTO #tmp VALUES (@lastID, @val)


CLOSE Cur
DEALLOCATE Cur
SELECTFROM #tmp
DROP TABLE #tmp

Główna sztuszka polega na tym, aby posortować dane pobierane do kursora taki sposób, jakbyśmy mieli te dane agregować standardowymi funkcjami COUNT lub MAX. To co umieszczamy normalnie w GROUP BY, teraz powinno się znaleźć w ORDER BY. To jest nasz klucz, po którym dane z poszczególnych rekordów będą łączone przecinkiem.

Główny algorytm działa dość prosto. Dopóki nasz klucz nie zmienia się, kumulujemy kolejne wartości w zmiennej @val. Ważna tutaj jest funkcja COALESCE, która pozwala odpowiednio ulokować przecinek. Jeżeli kolejny rekord pobrany z kursora ma inny klucz, skumulowany rekord wędruje do tabeli tymczasowej, a zmienna kumulująca @val jest zerowana. Na końcu pętli do tabeli tymczasowej zapisywana jest wartość skumulowana dla ostatniego klucza. Zastosowanie takiego algorytmu wymusza więc zapewnienie właściwego porządku danych w kursorze (te same klucze muszą być obok siebie). A to najłatwiej zrealizować odpowienim ORDER BY. W celu wyjaśnienia - druga kolumna w ORDER BY zapewnia alfabetyczną kolejność wartości łączonych przecinkiem w ramach jednego klucza. Jeżeli nie zależy nam na tym, można tę kolumnę tam pominąć. Myślę, że pozostałej części nie trzeba tłumaczyć, bo jest w miarę jasna.

Podsumowanie i wnioski

Zaprezentowane przykłady prezentują możliwe rozwiązania. Być może ktoś ma inne, lepsze, łatwiejsze. Zachęcam do dzielenia się tymi rozwiązaniami w komentarzach. Ja postaram się w najbliższym czasie pokazać, jak radzić sobie w jeszcze trudniejszych przypadkach i jak napisać własną instrukcję agregującą w .NET. Jest to na dłuższą metę najprzyjemniejsze i chyba najbardziej wydajne rozwiązanie. Wymaga jednak większego jednorazowego wysiłku.

Kategoria:AgregacjeSQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !