Concatenate an arbitrary length of columns per row

Question

How can I concatenate an arbitrary length of columns per row? I tried the following, but the function as is requires one to specify the column names:

SELECT CONCAT([C1], [C2], ...) FROM [dbo.table];

How can I achieve the same result without specifying each column name explicitly?

may I suggest a better table name ? Having something that looks like schema in the table name in the name is not good practive, having table in the name isn't either. Having period in the table name is just asking for trouble — t-clausen.dk, Oct 13 '15 at 11:13

score 1 · Accepted Answer · edited May 23 '17 at 10:27

You'd need to use dynamic SQL. You can query the system catalg view sys.columns to get the column names, and then use SQL Server's XML Extension to concatenate the rows to a single string giving your final SQL to execute:

DECLARE @TableName SYSNAME = 'dbo.YourTable';   

DECLARE @SQL NVARCHAR(MAX) = 'SELECT CONCAT(' + 
                            STUFF(( SELECT ',' + QUOTENAME(c.Name)
                                    FROM sys.columns c
                                    WHERE [object_id] = OBJECT_ID(@TableName)
                                    FOR XML PATH(''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ')
                            FROM ' + @TableName + ';';

EXECUTE sp_executesql @SQL;

ADDENDUM

If you want to delimit your columns, you can add a further concatenation while you are creating your column list:

DECLARE @TableName SYSNAME = 'dbo.YourTable',
        @Delimiter VARCHAR(10) = ', ';

DECLARE @SQL NVARCHAR(MAX) = 'SELECT CONCAT(' + 
                            STUFF(( SELECT ',''' + @Delimiter + ''',' + QUOTENAME(c.Name)
                                    FROM sys.columns c
                                    WHERE [object_id] = OBJECT_ID(@TableName)
                                    FOR XML PATH(''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, LEN(@Delimiter) + 5, '') + ')
                            FROM ' + @TableName + ';';

EXECUTE sp_executesql @SQL;

ADDENDUM 2

To avoid the delimiter being added when the value is null, e.g instead of ending up with:

1,,,2,3

You simply get

1,2,3

You need to slightly amend the logic, before it was generating a query that was like:

CONCAT([C1], ',', [C2], ',', [C3])

Instead you want:

CONCAT([C1], ',' + [C2], ',' + [C3])

Because you are now using ',' + [C2] if [C2] is null, the result will be null, so the delimiter will be removed:

DECLARE @TableName SYSNAME = 'dbo.YourTable',
        @Delimiter VARCHAR(10) = ', ';

DECLARE @SQL NVARCHAR(MAX) = 'SELECT CONCAT(' + 
                            STUFF(( SELECT ',''' + @Delimiter + ''' + ' + QUOTENAME(c.Name)
                                    FROM sys.columns c
                                    WHERE [object_id] = OBJECT_ID(@TableName)
                                    FOR XML PATH(''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, LEN(@Delimiter) + 7, '') + ')
                            FROM ' + @TableName + ';';
EXECUTE sp_executesql @SQL;

ADDENDUM 3

To remove the first column you can use ROW_NUMBER() on the sys.columns system catalog view, then exclude the first column:

DECLARE @TableName SYSNAME = 'dbo.YourTable',
        @Delimiter VARCHAR(10) = ', ';

DECLARE @SQL NVARCHAR(MAX) = 'SELECT CONCAT(' + 
                            STUFF(( SELECT ',''' + @Delimiter + ''' + ' + QUOTENAME(c.Name)
                                    FROM    (   SELECT name, 
                                                        RowNumber = ROW_NUMBER() OVER(ORDER BY column_id)
                                                FROM sys.columns c
                                                WHERE [object_id] = OBJECT_ID(@TableName)
                                            ) AS c
                                    WHERE c.RowNumber != 1 -- not first column
                                    FOR XML PATH(''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, LEN(@Delimiter) + 7, '') + ')
                            FROM ' + @TableName + ';';
EXECUTE sp_executesql @SQL;

Thank you Gareth! Your code runs without any error message on my SQL Server instance, but I don't get a result displayed. Do you know why this is? — JimBoy, Oct 13 '15 at 12:01
Have you updated the value of `@TableName` from `'dbo.YourTable'` to your actual table name? — GarethD, Oct 13 '15 at 12:43
Thank you so much! :) Is there a way to put a delimiter between the different strings? — JimBoy, Oct 13 '15 at 13:12
Nice! But now I see that `NULL` gets counted, too. Is there a simple way to suppress that? — JimBoy, Oct 13 '15 at 14:01
Thank you so much for your thoroughly explanation! :) Just one last question: Can I exclude the first column of the table from the process? — JimBoy, Oct 13 '15 at 14:31
I have added this edit, I would however strongly recommend that you actually take the time to pick apart the query and understand it, perhaps use `PRINT @SQL` to view the SQL that is being generated. While this script might be useful to you in the short term, it has very limited use if you don't understand what is going on, and as has happened, you need to ask a question about every minor modification. If you understand the script, then you will be able to make such amendments without any hassle. — GarethD, Oct 13 '15 at 14:51
Thank you!! :) I will definitely look up the different parts of your code since, as you correctly stated, I currently don't understand much of it. But this will get me going. :) — JimBoy, Oct 13 '15 at 14:55

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

You need to use Dynamic-SQL for this:

Warning:

I've used tempdb (tempdb.sys.columns) because I cannot create normal tables in demo. In your case use your normal database. And change condition to: WHERE object_id = OBJECT_ID('table_name').

LiveDemo

CREATE TABLE #tab(ID INT, C1 INT, C2 INT, C3 INT);

INSERT INTO #tab VALUES (1, 1,2,3), (2, 2,3,4);


DECLARE @cols  NVARCHAR(MAX);

SET @cols = STUFF(     
            (SELECT ',' + QUOTENAME(name)
            FROM tempdb.sys.columns
            WHERE 
              object_id = (SELECT object_id 
                           FROM tempdb.sys.objects 
                           WHERE NAME like '#tab%' AND Type = 'U')
            AND name LIKE 'C%'
            ORDER BY column_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


DECLARE @query NVARCHAR(MAX)=
  N'SELECT ID, CONCAT(<placeholder>) AS concated_columns FROM #tab';

SET @query =  REPLACE(@query, '<placeholder>', @cols);

EXEC [dbo].[sp_executesql]
       @query;

EDIT: If you need specific character between concatenated values use:

(SELECT ',' + CONCAT(QUOTENAME(name) , ','' ''' )

LiveDemo2

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 13 '15 at 11:23

Lukasz Szozda

162,964
23
234
275

Thank you so much, lad2025! This is exactly what I need. :) Unfortunately, I can't figure what I have to change to make it work in *my* database. – JimBoy Oct 13 '15 at 11:52
@JimBoy Change `tempdb.sys.columns` to `sys.columns`, `WHERE object_id = OBJECT_ID('table_name')` and `#tab` to `your_table_name`. Thats all ;) – Lukasz Szozda Oct 13 '15 at 11:57
Thank you for your help! I changed everything according to your explanation, but I don't get a result table displayed. – JimBoy Oct 13 '15 at 12:06
@JimBoy `AND name LIKE 'C%'` do you have columns like `C...` or `Purchased_...`? If yes change `AND name LIKE 'C%'` to `AND name LIKE 'Purchased%'` – Lukasz Szozda Oct 13 '15 at 12:07
1

N.B. The actual function `QUOTENAME()` is safer than just concatenating brackets, consider the following table - `CREATE TABLE #T ("Stupid[]Name" VARCHAR(5));` -- Using quotename would escape this correctly to give: `[Stupid[]]Name]`, whereas just concatenating brackets will give `[Stupid[]Name]`, resulting in a syntax error. While I admit this is an abhorrent object name, but sometimes people need to cater for this. – GarethD Oct 13 '15 at 12:54

score 0 · Answer 3 · answered Oct 13 '15 at 11:19

To concat all columns in arbitrary table:

DECLARE @Columns nvarchar(MAX)

SELECT @Columns = ISNULL(@Columns + ',','') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo'

DECLARE @sql nvarchar(MAX)

SET @sql = N'SELECT CONCAT('+@Columns+')
FROM dbo.TableName'

EXEC sp_executesql @sql

RoKa · Answer 4 · 2015-10-13T11:34:46.297

Use dynamic SQL and remember to cater for nulls!

declare @sql nvarchar(max), @t sysname, @c sysname  
select @sql = 'select ', @t = '[dbo].[CONTACTS]' /* <---- YOUR TABLE NAME HERE */

declare cols cursor for
    select name from sys.columns where object_id = object_id(@t) order by column_id 
open cols 
fetch next from cols INTO @c

while @@FETCH_STATUS = 0
 begin
    select @sql = @sql + 'convert(nvarchar(max), isnull(' + @c + ', '''')) + '  
    fetch next from cols INTO @c
 end 
close cols 
deallocate cols

select @sql = left(@sql, len(@sql)-2) + ' from ' + @t 
exec sp_executesql @sql

Dan Guzman · Answer 5 · 2015-10-13T11:41:33.057

0

If your table has a primary key column, you could use a correlated subquery like the example below. SqlFiddle here.

SELECT (
        ( SELECT    *
          FROM      dbo.table1 AS t2
          WHERE     t2.C1 = t1.C1 --specify primary key column(s) here
        FOR
          XML PATH('test')
            , TYPE
        )).value('/test[1]', 'nvarchar(MAX)') AS ConcatenatedValue
FROM    dbo.table1 AS t1;

edited Oct 13 '15 at 11:41

answered Oct 13 '15 at 11:28

Dan Guzman

43,250
3
46
71

Concatenate an arbitrary length of columns per row

5 Answers5