SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Need to output CSV, TSV, or other?

Ever have the need to create a CSV list in SQL Server? Not sure how? Starting in SQL Server 2017 we get a new native function to do exactly that. Specify a delimiter and you get a delimited list.

SQL 2016 brought us a native function for parsing a CSV to table (String_Split). It seems odd that there wouldn’t be a native function to create a CSV list until 2017. None the less, here it is:


Using the STRING_AGG function is really simple. Here’s how:

STRING_AGG([Column], ‘delimiter’)




What I like about this is that the current methods, such as a CTE often end up adding an extra comma at the end of the string that needs to be trimmed and are much slower than the native function.

Here’s a CTE for comparison:

;WITH dbname (database_id, Name) AS
SELECT B.database_id + 1, B.Name + A.Name + ‘, ‘
FROM (SELECT database_id, Name FROM sys.databases WHERE Name <> ”) A
INNER JOIN dbname B ON A.database_id = B.database_id
SELECT SUBSTRING(Name, 1, LEN(Name) -1) as name FROM (SELECT TOP 1 Name FROM dbname ORDER BY Name DESC) q

As you can see the native command has much less cost than the CTE:


I look forward to SQL Server 2017 and all the new features it will bring.

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.


Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...