SQL - zwracanie wielu wyników w pojedynczym wierszu

Od dłuższego czasu nie publikowałem nowych wpisów na blogu, czas to zmienić :)

Podczas ostatniego zadania potrzebowałem wyselekcjonować wyniki w jednym wierszu. Najłatwiej będzie mi wytłumaczyć zagadnienie na przykładzie:

Powyżej wycinek bazy danych z jakim na pewno nie jeden z Was spotkał się podczas codziennej pracy. Chciałbym wyselekcjonować id-ki przedmiotów dla każdego zamówienia. Nic trudnego:

select o.OrderID, si.StockItemID
from Sales.Orders o JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
 JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
order by o.OrderID;

Rezultat jak widać powyżej łatwy do przewidzenia - lista zamówień wraz z pozycjami zamówienia. Co jeżeli chcielibyśmy teraz przykładowo scalić wyniki do jednego wiersza?

2 - 10, 50
4 - 130, 260, 50
itd.

Dla większości baz danych istnieją gotowe funkcje które pozwalają w prosty sposób scalać wyniki do pojedynczego wiersza. MS SQL posiada przykładowo funkcję STRING_AGG która w najprostszej postaci przyjmuje tylko dwa argumenty: wyrażenie do agregacji oraz separator wyników.

select o.OrderID, STRING_AGG(si.StockItemID, ', ') as 'Ordered items Ids'
from Sales.Orders o JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
 JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
group by o.OrderID
order by o.OrderID;

Wynik:


 



 Możemy sobie łatwo wyobrazić, że chcielibyśmy w tym miejscu posiadać np. kody produktów, nazwy zakupionych dóbr. Co więcej otrzymane rezultaty możemy posortować. Przykład powyżej pokazuje, że agregowane dane nie uwzględniają kolejności Id zakupionych przedmiotów. Łatwo można to zmienić następującą konstrukcją:

select o.OrderID, STRING_AGG(si.StockItemID, ', ') WITHIN GROUP (ORDER BY si.StockItemID)  as 'Ordered items Ids'
from Sales.Orders o JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
 JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
group by o.OrderID
order by o.OrderID;

Rezultat:


 


Oczywiście podobne funkcje istnieją w innych silnikach bazodanowych, dla przykładu w Oracle - LISTAGG:

SELECT o.OrderID, LISTAGG(si.StockItemID, ', ') WITHIN GROUP (ORDER BY si.StockItemID) "Ordered items Ids"
from Sales.Orders o JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
 JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
group by o.OrderID
order by o.OrderID;