SQL 2017: Combine Concat_Ws with String_Agg
Author: jason
Date: 2019-01-19 13:58:09
Category: Musing

Oh my, we're getting crazy now, combining CONCAT_WS with STRING_AGG. What's going to happen!?? I'm on the edge of my seat!

Code


/*
String Aggregate with Concatenate With Strings? What??!!

Note, for display purposes, the query results have been sent to text
*/

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

SELECT STRING_AGG(CONCAT_WS(',', ISNULL([Name], ''), ISNULL([Description], ''), ISNULL([Quantity], '')), ',') AS OneLineCsv FROM #Temp;
SELECT STRING_AGG(CONCAT_WS(',', ISNULL([Name], ''), ISNULL([Description], ''), ISNULL([Quantity], '')), CHAR(10)) AS MultiLineCsv FROM #Temp;







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