Я хочу вернуть результаты select Column from Table
в строку с разделителями-запятыми, используя SQL Server.
Столбец достаточно большой (nvarchar(2000)
), поэтому решение должно иметь возможность обрабатывать очень большие значения результата.
Я хочу вернуть результаты select Column from Table
в строку с разделителями-запятыми, используя SQL Server.
Столбец достаточно большой (nvarchar(2000)
), поэтому решение должно иметь возможность обрабатывать очень большие значения результата.
DECLARE @result nvarchar(max)
SET @result = ''
SELECT @result = @result + [Column] + N','
FROM [TABLE]
--TODO: trim last ',' if you require
PRINT @result
Если Column
может быть нулевым, то либо сначала исключить его, либо использовать ISNULL
/COALESCE
- иначе один NULL
сломает всю последовательность. Эффективнее исключить его с помощью WHERE
:
SELECT @result = @result + [Column] + N','
FROM [TABLE]
WHERE [Column] IS NOT NULL
без конечной версии запятой:
declare @s varchar(max);
select @s = isnull(@s + ', ' + lastname, lastname)
from person
order by lastname;
print @s;
Я создал 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'', @[email protected] 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', @[email protected] OUTPUT
SET @csv = REPLACE(REPLACE(@csv, '<m_m>', ''), '</m_m>', CHAR(13) + CHAR(10))
END
GO
Использование:
DECLARE @csv nvarchar(max)
EXEC [dbo].[spQueryToCsv] @query = 'SELECT * FROM Customers', @csv = @csv OUTPUT, @orderBy = 'ORDER BY CustomerId'
SELECT @csv
Это основано на том же коде, который я написал, чтобы превратить произвольную таблицу в строку HTML.
У меня были проблемы с методом, обсуждаемым в предлагаемом ответе. Я взял код из SQLAuthority. Но это работает каждый раз, если у вас есть проблема, предлагаемая решение
SELECT SUBSTRING(
(SELECT ',' + s.ColumnName
FROM dbo.table s
ORDER BY s.ColumnName
FOR XML PATH('')),2,200000) AS CSV
GO
STRING_AGG добавлен в 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