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.

One thought on “Need to output CSV, TSV, or other?

  1. Thank you for posting this Daniel, this is very helpful, CSVs really is a handy way of getting data from one program to another where one program cannot read the other ones normal output.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s