SQL 2017: String Aggregate
Author: jason
Date: 2019-01-17 11:17:34
Category: Technical

This new string aggregate function in SQL 2017 is way cool. If you need to build a Column from Rows you don't need to use FOR XML anymore. STRING_AGG is the magic thing!!

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017


Code


/*
I am so excited about STRING_AGG

I no longer need to use FOR XML to build a String from Rows!!

Make my Rows into 1 Column!!!
*/

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp ([Name] VARCHAR(25), [Description] VARCHAR(255));
INSERT #Temp VALUES ('Apple','Red'), ('Apple','Green'), ('Plum','Purple');

SELECT STRING_AGG([Name], ',') AS NewMagical FROM #Temp;

SELECT COALESCE(STUFF((SELECT ',' + name AS [text()] FROM #Temp
FOR XML PATH('')), 1, 1, ''), '') AS OldLongBoring







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009