I want to return the results of select Column from Table
into a comma separated string using SQL Server.
The column in question is rather large ( nvarchar(2000)
) so the solution has to be able to handle very large result values.
I want to return the results of select Column from Table
into a comma separated string using SQL Server.
The column in question is rather large ( nvarchar(2000)
) so the solution has to be able to handle very large result values.
8条答案
按热度按时间vybvopom1#
STRING_AGG was added in sql 2017
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
bogh5gae2#
If
Column
can be null, then either exclude it first, or useISNULL
/COALESCE
- otherwise a singleNULL
will break the entire sequence. It is more efficient to exclude it with aWHERE
:ny6fqffe3#
without the trailing comma version:
wgeznvg74#
I've had problems with the method discussed in the suggested answer. I took the code from SQLAuthority. But this works every time if you have problem suggested solution
tjvv9vkg5#
I've created a proc that will dynamically create a CSV out of any arbitrary table, without needing to explicitly specify the columns. This is useful if you don't want to write custom code every time you want to turn a SQL table into a CSV string.
Usage:
This is based on similar code I wrote to turn an arbitrary table into an HTML string .
qybjjes16#
Inner
SELECT
will create CSV, andSTUFF()
will trim first comma and spaceYou can replace the separator with anything you want, just adjust
STUFF()
to trim it in the beginning.gjmwrych7#
If you are stuck on old databases and can't use STRING_AGG and don't want to trim after generating the string:
Option 1:
Option 2:
I like Option 1 because it is shorter. I have not done any performance testing, so I can't say which is better on that front.
If going with Option 1, be sure that @Comma is set after @Result, otherwise you will get an extra comma at the beginning of the string.
Tested on SQL Server 2008
cgfeq70w8#
I've written a udf that returns the column data as CSV