Separated (*) All Columns using delimeter

Question

I want to separate (*) all columns I get from selecting temp table into single strings. Without knowing column name.

Let's say the record only one.

Col1	Col2	Col3	...Cols
Res1	Res2	Res3	...Rest

Separate all columns into single column

CommaSeparatedCol.
Res1,Res2,Res3, ...Rest

I'm using SQL Server.

I was thinking using variable and looping through result and get each column values and assign to variable, but I don't know how to do it.

here is one example: https://stackoverflow.com/questions/41812941/how-to-create-comma-delimited-list-from-table-with-dynamic-columns — DaFi4, Jul 21 '21 at 16:25

score 0 · Answer 1 · answered Jul 21 '21 at 16:20

Assuming that if your table has multiple rows, you want multiple rows, you could do something like this. Note that all your values will be implicitly converted to a varchar or nvarchar, and so you are are allowing SQL Server to determine what it'll be.

Assuming you're on a recent version of SQL Server you can do this (if not, you'll need to use the old FOR XML PATH method for STRING_AGG and CONCAT for CONCAT_WS (and STUFF with both):

DECLARE @SchemaName sysname = N'YourSchema',
        @TableName sysname = N'YourTable';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT @SQL = N'SELECT CONCAT_WS('','',' + STRING_AGG(QUOTENAME(c.[name]),',') WITHIN GROUP (ORDER BY c.column_id) + N') AS YourColumns' + @CRLF +
              N'FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';'
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.[name] = @SchemaName
  AND t.[name] = @TableName

--PRINT @SQL; --Your best friend;
EXEC sys.sp_executesql @SQL;

No, not as written, @HafizhanAlWafi , because they aren't created in the same database, and because they have different name in the `sys` objects. Though that's a very late curve ball; you'll need to amend the above a little if you want it to work with Temporary tables instead/as well. — Thom A, Jul 22 '21 at 07:56

Separated (*) All Columns using delimeter

1 Answers1