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:

STRING_AGG

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

STRING_AGG([Column], ‘delimiter’)

Example:

SELECT STRING_AGG(name, ‘,’) FROM SYS.DATABASES

image

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 1, CAST(” AS NVARCHAR(MAX))
UNION ALL
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:

stringagg2

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.

Comments

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

Loading comments...