Select budujący listę wartości rozdzielaną przecinkami

28-Wrz-2012

Tym razem szukamy metody na uzyskanie listy wartości rozdzielanej przecinkami. Załóżmy, że interesuje nas lista kolorów. Zacznijmy od prostego zapytania, które zwróci listę unikalnych kolorów:

SELECT DISTINCT P.Color
from Production.Product AS P
WHERE P.Color IS NOT NULL
ORDER BY P.Color ASC

Oto efekt:

Jednak my wolelibyśmy zobaczyć te wartości w jednej zmiennej! Dorzućmy więc FOR XML PATH:

SELECT DISTINCT P.Color
from Production.Product AS P
WHERE P.Color IS NOT NULL
ORDER BY P.Color ASC
FOR XML PATH

 oto efekt:

Nie! Nie! To nie o to chodziło! Po co mi to <row> i <Color>. No więc pozbądźmy się tego:

  • Rozszerzmy FOR XML PATH do postaci FOR XML PATH(”). Wyeliminuje to znaczniki <row></row>
  • Ponieważ chcemy dostać listę bez <Color> ale zamiast tego rozdzielaną przecinkami dopiszmy wszędzie gdzie jest P.Color przecinek: ‚,’+P.Color

O tak:

 SELECT DISTINCT ‚,’+P.Color
from Production.Product AS P
WHERE P.Color IS NOT NULL
ORDER BY ‚,’+P.Color ASC
FOR XML PATH(”)

 Oto nowa postać wyniku:

No, prawie. Jedyne co zostało to pozbyć się przecinka na początku. Pomoże w tym funkcja STUFF. Oto kilka słów na jej temat z Book Online:

The STUFF function inserts a string into another string. It deletes a specified
length of characters in the first string at the start position and then inserts
the second string into the first string at the start position.

W naszym przypadku, trzeba poszukać pierwszego przecinka, wyciąć go (czyli od pierwszego przecinka jeden znak) i zastąpić go przez napis pusty ”:

SELECT stuff(
(select DISTINCT ‚,’+P.Color
from Production.Product AS P
WHERE P.Color IS NOT NULL
ORDER BY ‚,’+P.Color ASC
FOR XML PATH(”))
,1
,1
,”)

 Oto wynik:

Oczywiście, jeżeli chcesz wynik zapytania zapisać w zmiennej uzupełnij kod w ten sposób:

 DECLARE @result NVARCHAR(1000)
SELECT @result=stuff(
(SELECT DISTINCT ‚,’+P.Color
from Production.Product AS P
WHERE P.Color IS NOT NULL
ORDER BY ‚,’+P.Color ASC
FOR XML PATH(”))
,1
,1
,”)
SELECT @result
 

Pomyśleć, że mogłem tą jedną linijką usprawnić budowanie dynamicznej tabeli przestawnej…

Dodaj komentarz:

Autor: Rafał Kraik