STRING_AGG w raporcie: wiele numerów w jednej kolumnie, drukowanych raz na pozycję

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

STRING_AGG w zapytaniu raportu skleja numery seryjne i partie w jedną kolumnę, gdy raport drukuje wiersz na paletę, a numery należą do pozycji. ROW_NUMBER wybiera wtedy jedyny wiersz pozycji, w którym ta kolumna ma stanąć, żeby numery nie powtarzały się przy każdej palecie.

Funkcja STRING_AGG z klauzulą WITHIN GROUP skleja aktywne numery pozycji w jedną kolumnę w ustalonej kolejności. Funkcja okna ROW_NUMBER z podziałem na pozycje wskazuje pierwszy wiersz palety i tylko tam wstawia sklejony tekst. Pozostałe wiersze pozycji dostają NULL, więc numery drukują się dokładnie raz.

To wzorzec z nazwami przykładowymi, bez związku z konkretnym wdrożeniem. Napisaliśmy go od nowa i uruchomiliśmy na SQL Server 2022 Express w jednorazowej bazie, a wyniki w artykule są wynikami tego uruchomienia. Tabela numerów ma tu inną postać niż w artykule o indeksie filtrowanym, bo to osobne przykłady. Szersze tło o budowie zapytań dla wydruków znajdziesz w opisie raportowania w SQL, a tutaj zajmujemy się jednym wzorcem.

Raport z wierszem na paletę i numerami należącymi do pozycji

Przykład ma trzy tabele. Tabela pozycja_dokumentu opisuje pozycję dokumentu: numer dokumentu i artykuł. Tabela pozycja_paleta ma wiele wierszy na pozycję, bo ten sam artykuł może leżeć na kilku paletach, a dwa wiersze mogą nawet wskazywać tę samą paletę. Tabela numery_pozycji trzyma numery powiązane z pozycją, nie z paletą, i właśnie ta różnica rodzi cały problem: złączenie pozycji z paletami powiela numery tyle razy, ile jest wierszy palet.

Kolumna typ_numeru w tabeli numery_pozycji przyjmuje trzy wartości, opisane w tabeli poniżej. Wartość 0 w kolumnie czy_aktywny oznacza numer usunięty miękko: wiersz zostaje w tabeli, a raport go pomija.

Przykład. Znacznik BRAK jest elementem przykładu i oznacza pozycję, dla której numerów nie przewidziano.

CREATE TABLE dbo.pozycja_dokumentu (
    id_pozycji   int          PRIMARY KEY,
    nr_dokumentu varchar(20)  NOT NULL,
    artykul      nvarchar(40) NOT NULL);
CREATE TABLE dbo.pozycja_paleta (
    id_wiersza int           PRIMARY KEY,
    id_pozycji int           NOT NULL REFERENCES dbo.pozycja_dokumentu,
    nr_palety  varchar(20)   NOT NULL,
    ilosc      decimal(15,3) NOT NULL);
CREATE TABLE dbo.numery_pozycji (
    id             int IDENTITY  PRIMARY KEY,
    id_pozycji     int           NOT NULL REFERENCES dbo.pozycja_dokumentu,
    typ_numeru     varchar(4)    NOT NULL,                 -- SER numer seryjny, PAR partia, BRAK znacznik braku numerów
    wartosc_numeru nvarchar(100) NOT NULL DEFAULT N'',
    ilosc          decimal(15,3) NULL,
    czy_aktywny    bit           NOT NULL DEFAULT 1);       -- 0 = usunięty miękko

Przykład ilustracyjny. Nazwy tabel i kolumn są wymyślone.

Dane testowe opisują jeden dokument, DOK 26-1001, z czterema pozycjami. Artykuł A ma trzy wiersze palet, w tym dwa na PAL-0001. Ma też cztery aktywne numery seryjne i jeden usunięty. Artykuł B ma dwa wiersze palet i dwie partie. Artykuł C ma wyłącznie wiersz typu BRAK, a Artykuł D nie ma żadnego wiersza w tabeli numerów.

INSERT dbo.pozycja_dokumentu VALUES
 (1,'DOK 26-1001',N'Artykuł A'), (2,'DOK 26-1001',N'Artykuł B'),
 (3,'DOK 26-1001',N'Artykuł C'), (4,'DOK 26-1001',N'Artykuł D');
INSERT dbo.pozycja_paleta VALUES
 (1,1,'PAL-0001',2), (2,1,'PAL-0001',1), (3,1,'PAL-0002',1),
 (4,2,'PAL-0003',30), (5,2,'PAL-0004',22.5), (6,3,'PAL-0005',5), (7,4,'PAL-0006',8);
INSERT dbo.numery_pozycji (id_pozycji, typ_numeru, wartosc_numeru, ilosc, czy_aktywny) VALUES
 (1,'SER',N'SN-000125',NULL,1), (1,'SER',N'SN-000123',NULL,1),
 (1,'SER',N'SN-000126',NULL,1), (1,'SER',N'SN-000124',NULL,1),
 (1,'SER',N'SN-000999',NULL,0), (2,'PAR',N'LOT-0102',30,1),
 (2,'PAR',N'LOT-0101',22.5,1),  (3,'BRAK',N'',NULL,1);

Przykład ilustracyjny. Wartości są wymyślone.

Jedna kolumna z numerami przez STRING_AGG i WITHIN GROUP

Numery skleja wyrażenie tabelowe n: jedno zapytanie grupujące po pozycji. STRING_AGG przyjmuje wyrażenie do sklejenia i separator, tutaj przecinek ze spacją. Klauzula WITHIN GROUP (ORDER BY typ_numeru, wartosc_numeru) ustala kolejność elementów wewnątrz sklejonego tekstu, bo sama funkcja jej nie gwarantuje.

Warunek WHERE przepuszcza wyłącznie aktywne numery typów SER i PAR. Numer usunięty miękko oraz wiersz typu BRAK nie wchodzą do kolumny, co potwierdził test: usunięty SN-000999 nie pojawił się w wyniku.

Seria drukuje się jako sam numer, a partia jako numer z ilością. Wyrażenie CASE wybiera format, a FORMAT(ilosc, '0.###', 'pl-PL') zamienia 22,5 na tekst 22,5, a 30 na tekst 30, bez zbędnych zer. Trzeci argument z kulturą ma znaczenie, o czym piszemy w sekcji o pułapkach.

Cały składnik jest owinięty w CONVERT(nvarchar(max), ...). To świadoma decyzja: bez rzutowania na typ max wynik funkcji ma limit 8000 bajtów, a jego przekroczenie przerywa zapytanie błędem.

CREATE OR ALTER VIEW dbo.raport_numerow AS
WITH n AS (
    SELECT id_pozycji,
           STRING_AGG(CONVERT(nvarchar(max),
                      CASE WHEN typ_numeru = 'PAR'
                           THEN wartosc_numeru + N' (' + FORMAT(ilosc, '0.###', 'pl-PL') + N')'
                           ELSE wartosc_numeru END), N', ')
                      WITHIN GROUP (ORDER BY typ_numeru, wartosc_numeru) AS numery
    FROM dbo.numery_pozycji
    WHERE czy_aktywny = 1 AND typ_numeru IN ('SER', 'PAR')
    GROUP BY id_pozycji
)
SELECT p.id_pozycji, p.nr_dokumentu, p.artykul, w.id_wiersza, w.nr_palety, w.ilosc,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY w.id_pozycji
                                    ORDER BY w.nr_palety, w.id_wiersza) = 1
            THEN COALESCE(n.numery,
                          CASE WHEN EXISTS (SELECT 1 FROM dbo.numery_pozycji b
                                            WHERE b.id_pozycji = p.id_pozycji
                                              AND b.czy_aktywny = 1 AND b.typ_numeru = 'BRAK')
                               THEN N'numery nie dotyczą' END)
       END AS numery
FROM dbo.pozycja_dokumentu p
JOIN dbo.pozycja_paleta w ON w.id_pozycji = p.id_pozycji
LEFT JOIN n ON n.id_pozycji = p.id_pozycji;

Przykład ilustracyjny. Widok raportu: wyrażenie n skleja numery, a główne zapytanie wybiera wiersz pozycji.

Kolejność wynika z sortowania tekstu po typie numeru i jego wartości. Dla pozycji z samymi seriami oznacza to porządek alfabetyczny, więc SN-000123 stoi przed SN-000124 niezależnie od tego, w jakiej kolejności numery wpisano. Jeśli raport ma zachować kolejność wpisu, potrzebna jest kolumna z numerem kolejnym i sortowanie po niej.

Numery tylko w jednym wierszu pozycji

Główne zapytanie łączy pozycję z wierszami palet i dokłada kolumnę numery. ROW_NUMBER() OVER (PARTITION BY w.id_pozycji ORDER BY w.nr_palety, w.id_wiersza) numeruje wiersze w obrębie pozycji. Wiersz z numerem 1 dostaje sklejony tekst, a pozostałe NULL, bo wyrażenie CASE nie ma gałęzi ELSE.

Drugi składnik sortowania, id_wiersza, nie jest ozdobą. Artykuł A ma dwa wiersze na tej samej palecie PAL-0001. Samo ORDER BY nr_palety nie rozstrzygnęłoby, który z nich jest pierwszy, a dodatkowa kolumna daje jednoznaczny wybór. Podobny problem, czyli wybór jednego wiersza bez pełnego sortowania, opisujemy w artykule o TOP 1 bez ORDER BY.

Kuszącą alternatywą jest porównanie nr_palety z MIN(nr_palety) w obrębie pozycji. W teście takie porównanie postawiło numery w obu wierszach na PAL-0001, czyli w dwóch wierszach pozycji zamiast w jednym.

SELECT p.artykul, w.nr_palety,
       CASE WHEN w.nr_palety = MIN(w.nr_palety) OVER (PARTITION BY w.id_pozycji)
            THEN n.numery END AS numery
FROM dbo.pozycja_dokumentu p
JOIN dbo.pozycja_paleta w ON w.id_pozycji = p.id_pozycji
JOIN (SELECT id_pozycji, STRING_AGG(wartosc_numeru, N', ') AS numery
      FROM dbo.numery_pozycji
      WHERE czy_aktywny = 1 AND typ_numeru = 'SER'
      GROUP BY id_pozycji) n ON n.id_pozycji = p.id_pozycji
ORDER BY p.artykul, w.nr_palety, w.id_wiersza;

Przykład ilustracyjny. Wariant z pułapką: dwa wiersze Artykułu A mają numery, bo oba leżą na PAL-0001.

Poprawne zapytanie raportu zostaje proste: widok czytamy zwykłym SELECT z sortowaniem, w którym id_wiersza również rozstrzyga remis. Poniższa tabela pokazuje wynik uruchomienia.

SELECT nr_dokumentu, artykul, nr_palety, ilosc, numery
FROM dbo.raport_numerow
ORDER BY nr_dokumentu, artykul, nr_palety, id_wiersza;

Przykład ilustracyjny. Zapytanie raportu na widoku.

Przykład. Wynik uruchomienia na SQL Server 2022 Express.

Pozycja bez numerów i znacznik braku numerów

Pozycja bez numerów ma w wyniku dwie możliwości. Gdy w tabeli numery_pozycji istnieje aktywny wiersz typu BRAK, kolumna numery pokazuje adnotację „numery nie dotyczą”. Gdy nie ma ani numerów, ani znacznika, kolumna zostaje pusta, czyli ma NULL. W wyniku widać więc różnicę między pozycją ze znacznikiem a pozycją bez niego.

Wybór realizuje COALESCE(n.numery, CASE WHEN EXISTS (...) THEN ... END). COALESCE bierze najpierw sklejone numery, a dopiero gdy ich brak, sprawdza wiersz typu BRAK. W przykładzie Artykuł C ma wiersz BRAK i dostaje adnotację, a Artykuł D nie ma nic i zostaje z NULL. Tekst adnotacji jest elementem przykładu: można go zmienić, bo o jego pojawieniu się decyduje wyłącznie warunek EXISTS.

Pułapki STRING_AGG i FORMAT w wyniku raportu

Pierwsza pułapka dotyczy wersji. Według dokumentacji STRING_AGG pojawiła się w SQL Server 2017, więc starsze silniki jej nie mają. Funkcja jest dostępna na każdym poziomie zgodności, ale klauzula WITHIN GROUP wymaga poziomu 110 lub wyższego. Na SQL Server 2022 poziom 100 kończy się przy tworzeniu widoku błędem 102 „Incorrect syntax near '('”, a poziomy od 110 do 160 działają. Samo STRING_AGG bez WITHIN GROUP działa także na poziomie 100, a zależności od poziomu 140 nie ma. Dla SQL Server 2017 nie wykonaliśmy testu.

Druga pułapka to limit wyniku. W teście 500 numerów po 9 znaków w typie varchar dało tekst o długości 5498 znaków. Ten sam zbiór w nvarchar zakończył się błędem 9829, bo limit 8000 bajtów oznacza tam 4000 znaków, a tysiąc numerów w varchar kończy się tym samym błędem. Z rzutowaniem na varchar(max) wynik ma 10998 znaków.

Wynik STRING_AGG ma limit 8000 bajtów, dopóki jeden ze składników nie zostanie rzutowany na typ max.

WITH x AS (
    SELECT TOP (1000) CONCAT('SN-', RIGHT('000000' + CONVERT(varchar(6),
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 6)) AS wartosc_numeru
    FROM sys.all_columns)
SELECT LEN(STRING_AGG(CONVERT(varchar(max), wartosc_numeru), ', ')) AS dlugosc FROM x;

Przykład ilustracyjny. Tysiąc numerów po rzutowaniu na typ max daje tekst o długości 10998 znaków.

Kolejne pułapki zebraliśmy w tabeli. Wszystkie objawy zmierzyliśmy w jednorazowej bazie, a trzy z nich pokazują poniższe zapytania.

Przykład. Siedem pułapek z jednorazowej bazy testowej.

NULL jest w STRING_AGG pomijany, ale pusty tekst liczy się jako element i sortuje na początku, więc daje wynik zaczynający się od przecinka. Same wartości NULL dają NULL. Kolejność tekstowa ustawia SN-10 przed SN-9, bo porównuje znaki, a nie liczby.

-- pusty tekst jest elementem, NULL jest pomijany
SELECT STRING_AGG(wartosc_numeru, N', ') WITHIN GROUP (ORDER BY wartosc_numeru)
FROM (VALUES (N'SN-1'), (N''), (CAST(NULL AS nvarchar(20))), (N'SN-2')) v(wartosc_numeru);

-- kolejność tekstowa
SELECT STRING_AGG(wartosc_numeru, N', ') WITHIN GROUP (ORDER BY wartosc_numeru)
FROM (VALUES (N'SN-9'), (N'SN-10'), (N'SN-100')) v(wartosc_numeru);

Przykład ilustracyjny. Wyniki: „, SN-1, SN-2” oraz „SN-10, SN-100, SN-9”.

Kolejność wpisu da się odtworzyć tylko wtedy, gdy tabela ma kolumnę z numerem kolejnym. Wtedy ORDER BY id w klauzuli WITHIN GROUP daje wynik „SN-9, SN-10, SN-100”.

SELECT STRING_AGG(wartosc_numeru, N', ') WITHIN GROUP (ORDER BY id) AS numery
FROM (VALUES (1, N'SN-9'), (2, N'SN-10'), (3, N'SN-100')) v(id, wartosc_numeru);

Przykład ilustracyjny. Kolejność wpisu zamiast kolejności tekstowej.

Konkatenacja znakiem plus ma własną pułapkę. Gdy ilość partii ma NULL, cały element „numer (ilość)” staje się NULL i znika z wyniku bez żadnego komunikatu. W teście partia z pustą ilością nie trafiła do wyniku, a CONCAT zachował ją jako „LOT-2 ()”. Osobna pułapka dotyczy języka sesji: FORMAT bez trzeciego argumentu daje 22.5 przy angielskim i 22,5 przy polskim, a z kulturą pl-PL zawsze 22,5.

SELECT STRING_AGG(wartosc_numeru + N' (' + FORMAT(ilosc, '0.###', 'pl-PL') + N')', N', ') AS plus,
       STRING_AGG(CONCAT(wartosc_numeru, N' (', FORMAT(ilosc, '0.###', 'pl-PL'), N')'), N', ') AS concat_
FROM (VALUES (N'LOT-1', CAST(5 AS decimal(15,3))),
             (N'LOT-2', CAST(NULL AS decimal(15,3)))) v(wartosc_numeru, ilosc);

Przykład ilustracyjny. Plus gubi element z NULL, CONCAT go zachowuje.

Zapytania kontrolne na wyniku raportu

Po każdej zmianie widoku uruchamiamy dwa zapytania. Pierwsze szuka pozycji, które mają numery w więcej niż jednym wierszu: poprawny widok zwraca 0 wierszy. Drugie porównuje liczbę numerów w kolumnie z liczbą aktywnych numerów typów SER i PAR w tabeli. W teście wyszły pary 4 i 4 dla Artykułu A oraz 2 i 2 dla Artykułu B.

-- kontrola 1: pozycje z numerami w więcej niż jednym wierszu (oczekiwane 0 wierszy)
SELECT id_pozycji, artykul, COUNT(numery) AS wierszy_z_numerami
FROM dbo.raport_numerow
GROUP BY id_pozycji, artykul
HAVING COUNT(numery) > 1;

-- kontrola 2: liczba numerów w kolumnie a liczba aktywnych numerów w tabeli
SELECT r.artykul,
       (LEN(r.numery) - LEN(REPLACE(r.numery, N', ', N''))) / 2 + 1 AS w_kolumnie,
       t.ile AS w_tabeli
FROM dbo.raport_numerow r
JOIN (SELECT id_pozycji, COUNT(*) AS ile
      FROM dbo.numery_pozycji
      WHERE czy_aktywny = 1 AND typ_numeru IN ('SER', 'PAR')
      GROUP BY id_pozycji) t ON t.id_pozycji = r.id_pozycji
WHERE r.numery IS NOT NULL;

Przykład ilustracyjny. Drugie zapytanie zakłada, że sam numer nie zawiera ciągu „, ”.

Przykład: raport dokumentu wydania towarów z numerami

Raport dokumentu DOK 26-1001 zwraca wiersz na paletę. Artykuł A ma cztery numery seryjne od SN-000123 do SN-000126 i trzy wiersze palet, w tym dwa na PAL-0001. Widok z STRING_AGG i ROW_NUMBER pokazuje numery wyłącznie w pierwszym wierszu Artykułu A. Artykuł B ma dwie partie w jednym wierszu. Artykuł C ma wiersz typu BRAK, więc kolumna numery zawiera adnotację „numery nie dotyczą”, a Artykuł D nie ma żadnego wiersza w tabeli numerów i zostaje z NULL. Dwa zapytania kontrolne potwierdzają, że liczba numerów w kolumnie zgadza się z tabelą.

Ten sam wzorzec zadziała z innymi tabelami, o ile pozycja ma stały klucz, a numery znają ten klucz. Dwa kolejne wzorce z tej serii dotyczą tych samych numerów od innej strony: jak policzyć saldo lokalizacji w T-SQL oraz jak pilnować numerów w dokumencie przez indeks filtrowany. Wybór ceny z zakresów cennika jest tematem osobnego artykułu. Przegląd wszystkich artykułów sekcji jest w spisie artykułów o SQL Server, a ogólniejsze podejście do wydruków w opisie raportowania bazy danych.

Pytania przed budową własnego raportu z numerami

Zanim przeniesiesz wzorzec do własnego raportu, odpowiedz sobie na cztery pytania.

• Wiersz: czy w waszym raporcie jeden wiersz to paleta czy pozycja i w którym wierszu mają stać numery.
• Rozmiar: ile numerów może mieć największa pozycja i czy wynik zmieści się w polu wydruku.
• Kolejność: czy numery mają być ułożone alfabetycznie, czy zgodnie z kolejnością wpisu.
• Usunięte: czy usunięte numery mają zostać w tabeli ze znacznikiem, żeby raport mógł je pominąć.