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

You can also use orderby while grouping the strings.

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

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: