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.
Something like this perhaps:
-- Sample data
DECLARE @someTable TABLE (SomeID int identity, SomeTxt varchar(100));
INSERT @someTable VALUES ('row1'),('row2'),('row3');
-- Solution
SELECT ConcatinatedString =
STUFF
((
SELECT ','+SomeTxt
FROM @someTable
FOR XML PATH(''), TYPE
).value('.','varchar(100)'),1,1,'');
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)
nvarchar by replacing varchar?SELECT LEN(@COLS) ?Another solution you can try is this.
SELECT LTRIM(RTRIM(<ColumnName1>)) + ',',
LTRIM(RTRIM(<ColumnName2>)) + ',',
...
LTRIM(RTRIM(<ColumnNamen>)) + ','
FROM tempdb.dbo.IMTD
If you only want one row keep that top 1 In there like
SELECT TOP 1
LTRIM(RTRIM(<ColumnName1>)) + ',',
LTRIM(RTRIM(<ColumnName2>)) + ',',
...
LTRIM(RTRIM(<ColumnNamen>)) + ','
FROM tempdb.dbo.IMTD
The LTRIM and RTRIM will remove any white space and this should allow you to copy and paste the result set anywhere you may need it. You will need to do this for each columnname.
You can use the query below to get the column names from your temp table.
DECLARE @ColumnNames NVARCHAR(MAX)
SELECT
@ColumnNames= COALESCE(@ColumnNames +',','')+COLUMN_NAME
FROM
TempDB.INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = '#TempTableName'
