如何在不使用游标的情况下将表的一列转换为SQL Server中的csv字符串

I want to return the results of select Column from Table into a comma separated string using SQL Server.

我想使用SQL Server将select Column from Table的结果返回到逗号分隔的字符串中。

The column in question is rather large (nvarchar(2000)) so the solution has to be able to handle very large result values.

有问题的列相当大（nvarchar（2000）），因此解决方案必须能够处理非常大的结果值。

3 个解决方案

#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:

如果Column可以为null，则先排除它，或使用ISNULL / COALESCE - 否则单个NULL将破坏整个序列。使用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.

我创建了一个proc，可以从任意表中动态创建一个CSV，而无需显式指定列。如果您不想在每次要将SQL表转换为CSV字符串时编写自定义代码，这将非常有用。

-- 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.

这是基于我编写的类似代码将任意表转换为HTML字符串。

#1

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

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

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

PRINT @result