Z danymi zapisanymi w naszych bazach często musimy robić przeróżne rzeczy. Dopóki poruszamy się w naszym świecie czysto wirtualnym i bazującym na najdziwniejszych nawet abstrakcjach, wszystko wydaje się bardzo proste i przejrzyste. Niestety czasami musimy złożyć jakąś ofiarę z naszych danych na ołtarzu interfejsu użytkownika a tutaj dziać się potrafią dosłownie cuda.
Zwykle raporty czy inne widoki korzystają z prostych mapowań, gdzie jedno pole danych odpowiada jednej wyświetlanej wartości. Czasami jednak można natrafić na prawdziwe wyzwanie – trzeba kolekcję rekordów przedstawić jako jedną wartość i to jeszcze porozdzielaną np. przecinkami.
Jak sobie można z czymś takim poradzić? W przypadku interfejsu aplikacji niektórzy preferują wykorzystanie do tego wprost jej logiki. Pobierają sobie taką kolekcję, iterują po niej i sklejają. W przypadku raportów takie sztuczki mogą być jednak albo problematyczne albo wręcz niemożliwe do wykonania. Istnieją jednak sposoby (przedstawię poniżej trzy), które pozwalają nam rozwiązać problem wprost przy pomocy samego języka TSQL.
Sposób nr 1
Ta metoda jest przeznaczona wyłącznie szczęśliwych użytkowników SQL Servera w wersji 2017, gdzie pojawiła się gotowa funkcja STRING_AGG (można o niej poczytać tutaj) realizująca właśnie tę funkcjonalność. Ja jednak chciałbym się w tym wpisie skupić na reszcie populacji, która póki co musi się obejść niestety smakiem 😉
Sposób nr 2
Drugie rozwiązanie jakie chciałbym zaproponować bazuje głównie na części języka TSQL związanej z XML, o których pisałem w jednym z wcześniejszych wpisów.
Zanim przejdziemy dalej przygotujmy sobie tradycyjnie środowisko do zabawy. Tym razem będziemy operować książkami (aby nie robić nikomu darmowej reklamy posłużę się pozycjami zmyślonymi 😉 ) a naszym zadaniem będzie przygotowanie bibliografii.
declare @ksiazki as table ( idKsiazki int, tytul nvarchar(100), rokWydania int ); declare @autorzy as table ( idAutor int, idKsiazki int, imie nvarchar(50), nazwisko nvarchar(100) ); insert into @ksiazki (idKsiazki, tytul, rokWydania) values (1, N'Cudna książka o programowaniu', 2018), (2, N'Kolorujemy świat - poradnik dla początkujących grafików', 2016), (3, N'Czysty kod - tak, to możliwe', 2019), (4, N'Jak na czerwonym dywanie - podstawy CSS', 2017), (5, N'Zagmatwani, czyli słów kilka o relacyjnych bazach danych', 2019); insert into @autorzy (idAutor, idKsiazki, imie, nazwisko) values (1, 1, N'Jan', N'Kodujący'), (2, 2, N'Anna', N'Kredka'), (3, 2, N'Michał', N'Pastela'), (4, 3, N'Andrzej', N'Miotełka'), (5, 3, N'Mariusz', N'Szufelka'), (6, 3, N'Kamila', N'Czyścioch'), (7, 4, N'Dominika', N'Ścianka'), (8, 5, N'Dariusz', N'Tabelka'), (8, 6, N'Mikołaj', N'Zakładka');
Gdyby każda książka miała tylko jednego autora, wtedy pobranie wpisów bibliograficznych (co prawda uproszczonych, ale nie zajmujemy się przecież pisaniem publikacji tylko programowaniem 😉 ) mogłoby wyglądać tak jak poniżej.
select concat(a.imie, ' ', a.nazwisko, ', ', k.tytul, ', ', k.rokWydania) as bibliografia from @ksiazki as k left join @autorzy as a on a.idKsiazki = k.idKsiazki
W efekcie działania tego zapytania otrzymamy następujący zbiór rekordów
bibliografia ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Jan Kodujący, Cudna książka o programowaniu, 2018 Anna Kredka, Kolorujemy świat - poradnik dla początkujących grafików, 2016 Michał Pastela, Kolorujemy świat - poradnik dla początkujących grafików, 2016 Andrzej Miotełka, Czysty kod - tak, to możliwe, 2019 Mariusz Szufelka, Czysty kod - tak, to możliwe, 2019 Kamila Czyścioch, Czysty kod - tak, to możliwe, 2019 Dominika Ścianka, Jak na czerwonym dywanie - podstawy CSS, 2017 Dariusz Tabelka, Zagmatwani, czyli słów kilka o relacyjnych bazach danych, 2019
Jak można się było spodziewać pozycje z wieloma autorami powtarzają się. Aby temu zapobiec musimy dołączyć kolekcję autorów nie jako tabelę ale jako pojedynczą wartość związaną z daną książką. Aby sobie z tym poradzić skorzystamy z wspomnianego przeze mnie wcześniej XML. Zobaczmy co się stanie kiedy spróbujemy pobrać w ten sposób dane o autorach jednej z książek (najlepiej tej, gdzie tych autorów jest najwięcej). To co musimy wprowadzić na pewno to połączenie imienia z nazwiskiem oraz dodanie przecinka (który zamieścimy przed każdą pozycją).
select concat(', ', imie, ' ', nazwisko)
from @autorzy
where idKsiazki = 3
for xml path('')
Po wykonaniu skryptu naszym oczom powinien ukazać się mniej więcej taki widok:
XML_F52E2B61-18A1-11d1-B105-00805F49916B ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- , Andrzej Miotełka, Mariusz Szufelka, Kamila Czyścioch
Jak widać wygląda on całkiem przyzwoicie. Jedyne co w nim może razić to obecność zbędnego przecinka i spacji na początku. Aby się go pozbyć wykorzystamy gotową funkcję stuff (można o niej poczytać tutaj), która pozwoli nam zastąpić dwa pierwsze znaki pustym łańcuchem. Jej implementacja będzie wyglądała następująco
select stuff(( select concat(', ', imie, ' ', nazwisko)
from @autorzy
where idKsiazki = 3
for xml path('')
), 1, 2, '') as autorzy
Teraz nasza lista autorów książki wygląda już praktycznie tak jak powinna
autorzy ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Andrzej Miotełka, Mariusz Szufelka, Kamila Czyścioch
Teraz spróbujemy połączyć ten mechanizm w całość, aby lista generowała się dla każdej książki z listy.
select concat( stuff(( select concat(', ', imie, ' ', nazwisko)
from @autorzy
where idKsiazki = k.idKsiazki
for xml path('')
), 1, 2, ''),', ', k.tytul, ', ', k.rokWydania) as bibliografia
from @ksiazki as k
Wykonanie tego zapytania zwróci nam już porządnie przygotowane wpisy bibliograficzne.
bibliografia ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Jan Kodujący, Cudna książka o programowaniu, 2018 Anna Kredka, Michał Pastela, Kolorujemy świat - poradnik dla początkujących grafików, 2016 Andrzej Miotełka, Mariusz Szufelka, Kamila Czyścioch, Czysty kod - tak, to możliwe, 2019 Dominika Ścianka, Jak na czerwonym dywanie - podstawy CSS, 2017 Dariusz Tabelka, Zagmatwani, czyli słów kilka o relacyjnych bazach danych, 2019
W tym przypadku zwrócenie każdego wpisu jako kompletnej wartości może nie zawsze być odpowiednie. Czasem warto będzie rozbić to na poszczególne pola. Niemniej sama zasada działania się przez to nie zmieni (co można zobaczyć w poniższym fragmencie kodu).
select
stuff(( select concat(', ', imie, ' ', nazwisko)
from @autorzy
where idKsiazki = k.idKsiazki
for xml path('')
), 1, 2, '') as autorzy,
k.tytul,
k.rokWydania
from @ksiazki as k;
Osobiście bardzo lubię podeście, kiedy dane z bazy są zwracane od razu w formacie, który nie wymaga żadnych dodatkowych działań poza zwykłym zmapowaniem wartości do szablonu. Przede wszystkim wykonanie wszystkich operacji agregujących po stronie bazy uwalnia nas od niepotrzebnego przepychania dodatkowych danych pomiędzy aplikacją a bazą oraz ich późniejszego przygotowywania do wyświetlenia.
Sposób nr 3
Czy poza pokazanymi wyżej dwiema metodami istnieją inne, które również pozwalają nam połączyć wiele rekordów w jeden łańcuch? Osobiście znam i czasem korzystam z jeszcze jednej metody, którą lubię wykorzystywać szczególnie w przypadku kiedy połączone wartości można zapisać w jednej zmiennej np. w procedurze.
Aby skorzystać z przykładowych danych umówmy się, że chcemy pobrać dane tylko z jednej książki i zapisać ją do zmiennej @autorzyKsiazki. Najpierw pozwolę sobie zapisać kompletny kod a potem wyjaśnię jego działanie.
declare @autorzyKsiazki nvarchar(max) = null; select @autorzyKsiazki = concat(coalesce(@autorzyKsiazki + ', ', ''), imie, ' ', nazwisko) from @autorzy where idKsiazki = 3; select @autorzyKsiazki;
To rozwiązanie bazuje tak na prawdę na kilku prostych faktach z TSQL:
- Wykorzystanie operatora + w przypadku łączenia łańcuchów znaków, gdzie jeden nich ma wartość NULL, zawsze zwróci NULL (funkcja concat tak się nie zachowuje o czym można się łatwo przekonać),
- funkcja coalesce pozwala nam podstawić pusty łańcuch w momencie kiedy konkatenacja łańcuchów znaków zwraca wartość NULL,
- możemy dodawać kolejne treści do zmiennej iterując zapytaniem select po kolejnych rekordach.
Bardzo ważnym elementem umożliwiającym poprawne działanie tego kodu jest odpowiednia inicjalizacja zmiennej @autorzyKsiazki. Ustawienie tej wartości początkowo na pusty łańcuch znaków spowoduje błędne działanie – tam musi być wartość NULL. Aby to zrozumieć prześledźmy po kolei działanie tego zapytania:
- na początku zmienna @autorzyKsiazki ma wartość NULL,
- próba złączenia wartości tej zmiennej z przecinkiem i spacją zwróci nam również wartość NULL, którą zastępujemy pustym łańcuchem znaków,
- do owego pustego łańcucha dołączamy imię i nazwisko rozdzielone spacją (jeśli spodziewamy się tam wartości NULL to powinniśmy to uwzględnić aby nie dodawać niepotrzebnej spacji),
- teraz całą uzyskaną wartość przepisujemy do zmiennej @autorzyKsiazki i przechodzimy do kolejnego rekordu,
- przy drugim rekordzie wartość zmiennej @autorzyKsiazki nie jest już NULL więc zsumowanie jej z przecinkiem i spacją da nam wartość uzyskaną przy pierwszym rekordzie wraz z przecinkiem, za którym dodamy wartości z drugiego rekordu a całość znowu przypisujemy do zmiennej @autorzyKsiazki,
- każda kolejna iteracja doda nam do bieżącej wartości przecinek, spację oraz kolejnego autora.
Jak widać jest wiele sposobów na poradzenie sobie z przedstawionym problemem. Każdy z nich, pomimo oczywistych podobieństw (jak np. radzenie sobie z nadmiarowym delimiterem) może być mniej lub bardziej odpowiednim dla konkretnych przypadków. To na co jednak chciałbym tutaj najbardziej zwrócić uwagę to moje spostrzeżenie, że większość problemów związanych z obróbką i przygotowaniem danych można wykonać z powodzeniem przy pomocy samego języka TSQL. W końcu jeśli środowisko pozwala wręcz mnożyć różne rozwiązania to każdy ma szansę znaleźć w nich coś dla siebie i ułatwić sobie w ten sposób pracę 🙂
