Załóżmy, że w kolumnie przechowywana jest lista napisów np imion porozdzielana przecinkami. Chcielibyśmy jedną komendą SQL parsować ten napis i wyłuskać z niego imiona, zwracając je w postaci tabeli. W tym artykule o tym jak można to zrobić bez pisania procedur czy funkcji, ale z wykorzystaniem CTE.
Zacznijmy od utworzenia testowej tabeli i wypełnienia jej danymi:
1 2 3 4 5 |
CREATE TABLE Napisy ( id int, txt VARCHAR(100)) GO |
1 2 |
INSERT INTO Napisy VALUES(1,'Janek,Zosia,Gosia') INSERT INTO Napisy VALUES(2,'Zbyszek,Agata,Lukasz,Maciek') |
1 |
<a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/cte2_1.png"><img class="alignnone size-full wp-image-427" title="cte2_1" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/cte2_1.png" alt="" width="447" height="155" /></a> |
Kolejnym krokiem będzie stworzenie wyrażenia Common Table Expression (CTE). Ale przyjrzyjmy się najpierw prostym zapytaniom
1 |
SELECT * FROM Napisy |
1 2 3 4 5 6 7 |
SELECT id AS 'id', txt AS 'txt', SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie', SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta, 0 AS Poziom FROM Napisy |
Wyrażenia SUBSTRING wycinają
- pierwsze imię z napisu
- resztę bez pierwszego imienia
No dobrze, ale wycięliśmy tylko jedno imię, a co z resztą!? Trzeba by było dobudować do naszego zapytania rekurencję, a tę możliwość daje nam CTE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH MyCTE AS ( SELECT id AS 'id', txt AS 'txt', SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie', SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta, 0 AS Poziom FROM Napisy UNION ALL SELECT n.id, m.reszta, SUBSTRING(m.reszta,1,charindex(',',m.reszta+',')-1), SUBSTRING(m.reszta,charindex(',',m.reszta+',')+1, len(m.reszta)) , m.Poziom+1 FROM Napisy n INNER JOIN MyCTE m on m.id = n.id WHERE m.reszta<>'' ) |
1 2 |
SELECT * FROM MyCTE order by id,poziom |
Na uwagę zasługuje druga część zapytania po union all.
- Pobieram rekord z tabeli Napisy n łącząc go z odwołaniem do MyCTE, co daje mi właśnie rekurencję! Rekurencja odbywa się dla każdego rekordu oddzielnie, bo wymaga tego warunek m.id=n.id
- Wycinanie pierwszego imienia odbywa się na podstawie listy imion w „reszcie” imion z poprzedniego poziomu rekurencji
- Tekst pozostały po usunięciu imienia zapamiętuję w nowej reszcie
- Wykonuję tę rekurencję tak długo, jak długo reszta napisu jest niepusta
- Kolumna poziom informuje mnie jedynie o tym, na którym poziomie rekurencji powstał dany rekord.
Jeżeli popatrzysz na wynik CTE wycinającego kolejne części napisu rozdzielanego przecinkami, widać, że reszta jest z każdym poziomem coraz krótsza, a wycinane imię jest zawsze pierwszym imieniem z listy. Poziom informuje na którym etapie wykonaliśmy to wycięcie.
Gdyby teraz chodziło o utworzenie e-maili na podstawie takiej listy, to rozważ delikatną zmianę ostatniego zapytania:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH MyCTE AS ( SELECT id AS 'id', txt AS 'txt', SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie', SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta, 0 AS Poziom FROM Napisy UNION ALL SELECT n.id, m.reszta, SUBSTRING(m.reszta,1,charindex(',',m.reszta+',')-1), SUBSTRING(m.reszta,charindex(',',m.reszta+',')+1, len(m.reszta)) , m.Poziom+1 FROM Napisy n INNER JOIN MyCTE m on m.id = n.id WHERE m.reszta<>'' ) |
1 |
SELECT imie+'@mojapoczta.pl' FROM MyCTE |
1 |
<a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/cte2_3.png"><img class="alignnone size-full wp-image-429" title="cte2_3" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/cte2_3.png" alt="" width="186" height="183" /></a> |
CTE dlatego się chwali, że szybko niejeden problem rozwali!
Więcej o CTE na mobilo (mobilo24):