How to turn one column of a table into a csv string in SQL Server without using a cursor

Question

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.

Answer 1

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

SELECT STRING_AGG (ColumnName, ',') AS csv 
FROM TableName
GROUP BY ColumnName2

Answer 2

DECLARE @result nvarchar(max)
SET @result = ''

SELECT @result = @result + [Column] + N','
FROM [TABLE]

--TODO: trim last ',' if you require

PRINT @result

If Column can be null, then either exclude it first, or use ISNULL/COALESCE - otherwise a single NULL will break the entire sequence. It is more efficient to exclude it with a WHERE:

SELECT @result = @result + [Column] + N','
FROM [TABLE]
WHERE [Column] IS NOT NULL

Answer 3

without the trailing comma version:

declare @s varchar(max);

select @s = isnull(@s + ', ' + lastname, lastname)
from person
order by lastname;

print @s;

Answer 4

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

SELECT SUBSTRING(
(SELECT ',' + s.ColumnName
FROM dbo.table s
ORDER BY s.ColumnName
FOR XML PATH('')),2,200000) AS CSV
GO

Answer 5

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.

-- Description: Turns a query into a formatted CSV.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
CREATE PROC [dbo].[spQueryToCsv] 
(
  @query nvarchar(MAX), --A query to turn into CSV format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @csv nvarchar(MAX) = NULL OUTPUT --The CSV output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = '';
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');  

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);

    SELECT * INTO #dynSql FROM (' + @query + ') sub;    

    SELECT @cols = ISNULL(@cols + '' + '''','''' + '', '''') + ''''''"'''' + ISNULL(REPLACE(CAST(['' + name + ''] AS nvarchar(max)), ''''"'''', ''''""''''), '''''''') + ''''"''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;        

    SET @cols = ''
      SET @csv = (SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''m_m''''));
      ''
    EXEC sys.sp_executesql @cols, N''@csv nvarchar(MAX) OUTPUT'', @csv=@csv OUTPUT    

    SELECT @headerRow = ISNULL(@headerRow + '','', '''') + ''"'' + REPLACE(name, ''"'', ''""'') + ''"''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = @headerRow + CHAR(13) + CHAR(10);

    SET @csv = @headerRow + @csv;    
    ';

  EXEC sys.sp_executesql @realQuery, N'@csv nvarchar(MAX) OUTPUT', @csv=@csv OUTPUT
  SET @csv = REPLACE(REPLACE(@csv, '<m_m>', ''), '</m_m>', CHAR(13) + CHAR(10))
END

GO

Usage:

DECLARE @csv nvarchar(max)
EXEC [dbo].[spQueryToCsv]  @query = 'SELECT * FROM Customers', @csv = @csv OUTPUT, @orderBy = 'ORDER BY CustomerId'
SELECT @csv

This is based on similar code I wrote to turn an arbitrary table into an HTML string.

Answer 6

Inner SELECT will create CSV, and STUFF() will trim first comma and space

SELECT STUFF( 
    (SELECT ', ',  Column FROM Table FOR XML PATH(''))
, 1, 2, '' )

You can replace the separator with anything you want, just adjust STUFF() to trim it in the beginning.

Answer 7

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:

DECLARE @Comma varchar(10) = '';
DECLARE @Result varchar(MAX) = '';

SELECT @Result = @Result + @Comma + Column, @Comma = ','
FROM Table;

Option 2:

DECLARE @Result varchar(MAX) = '';

SELECT @Result = @Result + CASE WHEN LEN(@Result) > 0 THEN ',' ELSE '' END + Column
FROM Table;

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

Answer 8

I've written a udf that returns the column data as CSV

CREATE FUNCTION [dbo].[UDF_GET_COLUMNS_IDS_AS_CSV]
(

)
RETURNS VARCHAR(1000)
AS
BEGIN

DECLARE @CSV_VALUES VARCHAR(1000) = ''

--CREATE A TEMP TABLE TO STORE THE IDS
DECLARE @TEMP_TABLE TABLE(ID INT IDENTITY(1,1), COLUMN_ID INT)

--INSERT THE DATA INTO TEMP TABLE
INSERT INTO @TEMP_TABLE
SELECT COLUMNID FROM TABLE_NAME

DECLARE @LOOP_COUNTER INT =1
DECLARE @REC_COUNT INT 

SELECT @REC_COUNT = COUNT(1) FROM @TEMP_TABLE

DECLARE @TEMP_COLUMN_ID INT

--LOOP THROUGH ALL THE RECORDS
WHILE @LOOP_COUNTER <= @REC_COUNT
BEGIN
    SELECT @TEMP_COLUMN_ID = COLUMN_ID FROM @TEMP_TABLE WHERE ID = @LOOP_COUNTER

    --IF IT'S NOT THE LAST REC THEN ONLY ADD COMMA
    IF @LOOP_COUNTER < @REC_COUNT
        SET @CSV_VALUES = @CSV_VALUES + CAST(@TEMP_COLUMN_ID AS VARCHAR(100)) + ',' 
    ELSE
        SET @CSV_VALUES = @CSV_VALUES + CAST(@TEMP_COLUMN_ID AS VARCHAR(100)) 

    SET @LOOP_COUNTER = @LOOP_COUNTER + 1
END



RETURN @CSV_VALUES

END

GO

Answer 9

Another simple approach to achieve in a single statement...

SELECT Replace((SELECT <FIELD NAME> + ', ' FROM <TABLE NAME> FOR XML PATH(''))+'{END}',', {END}','')

The {END} can be replaced with any string that wouldn't appear anywhere else in the results - it's just used to identify the last comma.