STRING_AGG() pro konečně smysluplně řeší spojování řetězců v datových setech

Funkce STRING_AGG(), novinka v SQL Serveru 2017, řeší nekonečné problémy s tím, jak spojit dohromady řetězce z více řádků v rámci jednoho datového setu. V průběhu let jsme pracovali nejčastěji s využitím FOR XML PATH (nebo JSON), případně s deklarací proměnné a sčítáním řetězců. To vše už nyní budeme používat zřejmě jen tehdy, vyžádá-li si to zpětná kompatibilita.

Pojďme si ukázat, jak STRING_AGG() funguje v praxi.K tomu si nejprve vytvoříme jednoduchá testovací data: objednávky a jejich položky:

DROP TABLE [dbo].[OrderItems]
DROP TABLE [dbo].[Orders]
GO

-- create sample tables
CREATE TABLE dbo.Orders
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME NOT NULL
)
GO

CREATE TABLE dbo.OrderItems 
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL REFERENCES dbo.Orders,
	ProductName VARCHAR(100) NOT NULL,
    Amount INT NOT NULL,
    Price INT NOT NULL,
    Note VARCHAR(50)
)
GO

-- populate tables with data
INSERT dbo.Orders VALUES ('2018-01-01')
INSERT dbo.Orders VALUES ('2018-02-02')
INSERT dbo.Orders VALUES ('2018-03-03')
INSERT dbo.Orders VALUES ('2018-03-04')
GO

INSERT dbo.[OrderItems]
	( [OrderId], [ProductName], [Amount], [Price], [Note] )
    SELECT Id, 'ProductA', 10, 1500, 'NoteOrderItem1' FROM dbo.Orders WHERE [Id] = 1 UNION ALL
    SELECT Id, 'ProductB', 5, 750, 'NoteOrderItem2' FROM dbo.Orders WHERE [Id] = 1 UNION ALL
	SELECT Id, 'ProductC', 3, 500, 'NoteOrderItem3' FROM dbo.Orders WHERE [Id] = 1 UNION ALL
	SELECT Id, 'ProductA', 15, 300, 'NoteOrderItem1' FROM dbo.Orders WHERE [Id] = 2 UNION ALL
	SELECT Id, 'ProductX', 8, 200, 'NoteOrderItem2' FROM dbo.Orders WHERE [Id] = 2 UNION ALL
	SELECT Id, 'ProductF', 15, 300, 'NoteOrderItem1' FROM dbo.Orders WHERE [Id] = 3 UNION ALL
	SELECT Id, 'ProductG', 8, 200, 'NoteOrderItem2' FROM dbo.Orders WHERE [Id] = 3
GO

-- review test data
SELECT * FROM [dbo].[Orders] [o]
SELECT * FROM [dbo].[OrderItems] [oi]
GO

Na daty si postupně vyzkoušíme různé použití STRING_AGG() funkce a to tak, že budeme agregovat hodnoty sloupce ProductName pro jednotlivé objednávky a názvy produktů od sebe oddělíme čárkou:

SELECT o.[Id], o.[OrderDate], 
       STRING_AGG(oi.[ProductName], ', ') Products
FROM dbo.[Orders] [o]
	LEFT JOIN [dbo].[OrderItems] [oi] ON [oi].[OrderId] = [o].[Id]
GROUP BY o.[Id], o.[OrderDate]
GO

Toto je nejjednodušší možné použití funkce STRING_AGG(): prostá agregace hodnot sloupce ProductName za sebou, jednotlivé položky odděleny čárkou. Všimněte si jedné opravu zásadní věci: STRING_AGG() nepřidává separátor za poslední položku v seznamu, což je naprosto úžasné, neboť jinak bychom to v 99% případů ještě obalovali dalším zbytečným kódem, abychom odstranili čásku za poslední položkou.

V rámci funkce STRING_AGG() můžeme používat i různé expressions a se slučovanými řetězci dále libovolně pracovat. V následujícím příkladu např. pomocí ISNULL() šetříme chybějící produkty pro případ, že objednávky nemá žádné položky:

SELECT o.[Id], o.[OrderDate], 
       STRING_AGG(ISNULL(oi.[ProductName], '0 order items'), ', ') Products
FROM dbo.[Orders] [o]
	LEFT JOIN [dbo].[OrderItems] [oi] ON [oi].[OrderId] = [o].[Id]
GROUP BY o.[Id], o.[OrderDate]
GO

Funkci STRING_AGG() můžeme použít i s přídavkem WITH GROUP(), čímž získáme možnost určit pořadí, v jakém budou řetězce spojovány:

SELECT o.[Id], o.[OrderDate], 
       STRING_AGG(ISNULL(oi.[ProductName], '0 order items'), ', ')
           WITHIN GROUP (ORDER BY oi.ProductName DESC) AS Products
FROM dbo.[Orders] [o]
	LEFT JOIN [dbo].[OrderItems] [oi] ON [oi].[OrderId] = [o].[Id]
GROUP BY o.[Id], o.[OrderDate]
GO

Nyní jsou názvy produktů seřazeny podle abecedy obráceně. Pro řazení můžeme používat libovolné dostupné sloupce v rámci dotazu, tedy klidně řadit podle hodnot z jiných tabulek, než ve které je náš agregovaný řetězec.

Myslím, že STRING_AGG() je opravdu podařená funkce a podle mě se stane vůbec nejpoužívanější novinkou v SQL Serveru 2017.