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

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?

Komentarze są wyłączone