M.R. M.R. - 1 month ago 9
SQL Question

Return SELECT query result as a CSV string

I have the following Sql Server 2016 SELECT statement that returns only 1 row:

SELECT TOP 1 * FROM tempdb.dbo.IMTD


How can I concatenate the values as a comma delimited string? NOTE: the column names of this temporary table are unknown as they can variate.

Thank you.

Answer

You can use Dynamic query as below:

DECLARE @COLS VARCHAR(MAX) = ''

SELECT @COLS = @COLS + ',' + COLUMN_NAME
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#table[_]%' -- Dynamic Table (here, Temporary table)

DECLARE @COLNAMES VARCHAR(MAX) = REPLACE(STUFF(@COLS, 1, 1, ''), ',', '+ '','' +')

Declare @cmd varchar(max) = 'Select ' + @COLNAMES + ' as CSVCol from #table'
-- will generate
-- Select Column1+ ',' +Column2+ ',' +Column3 as CSVCol from #table
EXEC (@cmd)