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 ASCOto 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 PATHoto 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 specifiedlength 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 @resultPomyśleć, że mogłem tą jedną linijką usprawnić budowanie dynamicznej tabeli przestawnej…
