New functions STRING_AGG() and CONCAT_WS() in Azure SQL

Microsoft has recently added two new string functions STRING_AGG  and CONCAT_WS to its cloud offering RDBMS solution i.e Azure SQL. These two functions are not new in the market and were already available in other databases like Postgres, Oracle, MySQL and Hive etc. Just to give an idea on SQL Azure, it is a cloud database from Microsoft based on SQL Server database technology.

STRING_AGG()

Dataset:

ProductCategoryID Category ProductNumber
40 Pumps PU-0452
40 Pumps PU-M044
10 Brakes RB-9231
10 Brakes FB-9873

Output:

ProductCategoryID Category ProductNumbers
10 Brakes RB-9231, FB-9873
40 Pumps PU-0452, PU-M044

SELECT
	pc.ProductCategoryID
	, pc.Name AS Category
	, STRING_AGG(ProductNumber, ', ') AS ProductNumbers
FROM SalesLT.ProductCategory pc
INNER JOIN SalesLT.Product p ON pc.ProductCategoryID = p.ProductCategoryID
WHERE pc.ProductCategoryID IN (10,40)
GROUP BY pc.Name, pc.ProductCategoryID

You can also use orderby while grouping the strings.


SELECT
	pc.ProductCategoryID
	, pc.Name AS Category
	, STRING_AGG(ProductNumber, ', ') WITHIN GROUP (ORDER BY productid DESC) AS ProductNumbers
FROM SalesLT.ProductCategory pc
INNER JOIN SalesLT.Product p ON pc.ProductCategoryID = p.ProductCategoryID
WHERE pc.ProductCategoryID IN (10,40)
GROUP BY pc.Name, pc.ProductCategoryID
ProductCategoryID Category ProductNumbers
10 Brakes FB-9873, RB-9231
40 Pumps PU-M044, PU-0452

CONCAT_WS()

CustomerID FirstName LastName AddressLine1 City StateProvince PostalCode
29545 John Beaver 1318 Lasalle Street Bothell Washington 98011
29559 Robert Bernacchi 2681 Eagle Peak Bellevue Washington 98004
29816 Keith Harris 7943 Walnut Ave Renton Washington 98055
30018 Virginia Miller 8713 Yosemite Ct. Bothell Washington 98011

Output:

CustomerID FirstName LastName FullAddress
29545 John Beaver 1318 Lasalle Street, Bothell, Washington, 98011
29559 Robert Bernacchi 2681 Eagle Peak, Bellevue, Washington, 98004
29816 Keith Harris 7943 Walnut Ave, Renton, Washington, 98055
30018 Virginia Miller 8713 Yosemite Ct., Bothell, Washington, 98011

SELECT c.CustomerID, c.FirstName, c.LastName,CONCAT_WS(', ', a.AddressLine1, a.City, a.StateProvince, a.PostalCode) AS FullAddress
FROM SalesLT.Customer c
INNER JOIN SalesLT.CustomerAddress ca ON c.CustomerID = ca.CustomerID
INNER JOIN SalesLT.Address a ON ca.AddressID = a.AddressID
WHERE ca.AddressType = 'Shipping' AND a.StateProvince = 'Washington'

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
23 − 18 =