Jak W SQL utworzyć z rekordów listę rodzielaną przecinkami CSV? Wykorzystanie typu tablicowego

16-Wrz-2011

Załóżmy, że pracujemy z tabelą zawierającą jakieś symbole, które mają np zostać załadowane do kontrolki typu listbox, tak by użytkownik mógł wybrać jeną z pozycji za pomocą myszki. Często tego rodzaju czynność wiąże się z przekształceniem listy rekordów w jedną zmienną zawierającą wypisane symbole porozdzielane za pomocą przecinka, średnika lub innego znaku w notacji popularnie zwanej CSV (Comma Separated Values).

Niech naszą tabelą będzie:

CREATE TABLE Countries
(Symbol VARCHAR(3),
Name VARCHAR(30),
GroupId INT);

Wstawiamy do niej rekordy państw:

INSERT Countries VALUES
(‚EN’, ‚England’,1),
(‚PL’, ‚Poland’,1),
(‚G’,’Germany’,1),
(‚CDN’,’Canada’,2),
(‚USA’,’USA’,2)

Opisane zadanie możesz zrealizować następującym zapytaniem:

DECLARE @CSV VARCHAR(1000);
SET @CSV = ”
SELECT @CSV=@CSV+Symbol+’,’ FROM Countries
SELECT @CSV
GO

Innymi słowy deklarujesz zmienną napisową, którą z rekordu na rekord aktualizujesz o kolejne państwo i przecinek (średnik czy inny znak rodzielający).

W efekcie w/w zapytania otrzymasz:

EN,PL,G,CDN,USA,

Cóż, gdyby miał cię denerwować przecinek na końcu tej listy usuń go modyfikując zapytanie następująco:

DECLARE @CSV VARCHAR(1000);
SET @CSV = ”
SELECT @CSV=@CSV+Symbol+’,’ FROM Countries
SELECT SUBSTRING(@CSV,1,LEN(@CSV)-1)
GO

Teraz wynik zapytania przedstawia się tak:

EN,PL,G,CDN,USA

Można dalej komplikować nasz przykład. Gdyby tak zechcieć z naszej tabeli wygenerować 2 listy porozdzielane przecinkami tak by państwa z jednej grupy znalazły się w tej samej liście? Moja propozycja jest taka: zróbmy funkcję, która będzie tworzyć listę CSV dla jednej grupy, a potem skonstruujemy zapytanie, które z tej funkcji skorzysta:

CREATE FUNCTION GetCSVCountries(@GroupId INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @CSV VARCHAR(1000);
SET @CSV = ”
SELECT @CSV=@CSV+Symbol+’,’ FROM Countries WHERE GroupID = @GroupId
RETURN SUBSTRING(@CSV,1,LEN(@CSV)-1)
END

A oto zapytanie korzystające z tej funkcji:

SELECT GroupID, dbo.GetCSVCountries(GroupId)
FROM
(SELECT DISTINCT GroupId FROM Countries) s

Podzapytanie SELECT DISTINCT ma za zadanie ‚wyłuskać’ z tabeli unikalne identyfikatory grup. ‚s’ na końcu ostatniej linijki to alias podzapytania. Składnia wymaga, aby on występował. Efekt podzapytania jest taki, że wyświetliłby rekord 1 i rekord 2. Ta jedynka i dwójka jest dalej przekazana do zewnętrzego zapytania, które z kolei tworzy listy CSV:

GroupID     
----------- -------------
1           EN,PL,G
2           CDN,USA

A teraz zróbmy coś jeszcze innego. Przygotujemy ‚uniwersalną funkcję’, która z rekordów zawierających jakiś symbol utworzy listę CSV. Skorzystamy z funkcji, która jako parametr przyjmie tabelę z symbolami. Tak jest – parametrem os SQL 2008 może być tabela (zobacz więcej na mobilo/mobilo24 tutaj.

Najpierw tworzymy typ tablicowy:

CREATE TYPE SymbolTable AS TABLE
( Symbol VARCHAR(3) )
GO

A potem funkcję:

CREATE FUNCTION GetCSV2( @TableName SymbolTable READONLY)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @CSV VARCHAR(1000);
SET @CSV = ”
SELECT @CSV=@CSV+Symbol+’,’ FROM @TableName
RETURN SUBSTRING(@CSV,1,LEN(@CSV)-1)
END

Pozostaje skorzystać z tej funkcji:

DECLARE @tab SymbolTable
INSERT @tab
SELECT Symbol
FROM Countries WHERE GroupId=1
SELECT dbo.GetCSV2(@tab)

Deklarujemy zmienną typu tablicowego, wpisujemy do tej tablicy rekordy z jednej tylko grupy, a potem wywołujemy funkcję, która przerobi zmienną tablicową na listę CSV.

A ty jak wykorzystujesz typ tablicowy?

Dodaj komentarz:

Autor: Rafał Kraik