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:
(
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:
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:
| ID | Name |
| 1 | borsuki |
| 2 | kuny |
| 1 | konie |
| 2 | surykatki |
| 3 | marchewki |
| 4 | bakłażany |
| 1 | psy |
Naszym celem jest napisanie skryptu, który zwróci wynik w takiej postaci:
| ID | Names |
| 1 | borsuki,konie,psy |
| 2 | kuny,surykatki |
| 3 | marchewki |
| 4 | bakł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:
(
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:
(
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
SELECT * FROM #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
Brak komentarzy - bądź pierwszy