How to turn one column of a table into a csv strin

问题:

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.

回答1:

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

回答2:

without the trailing comma version:

declare @s varchar(max);

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

print @s;

回答3:

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.

回答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

回答5:

STRING_AGG was added in sql 2017

https://docs.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