CODEWITHSUNDEEP
sql-server
Ivan-Mark Debono
Ivan-Mark Debono

Reputation: 16300

Concatenate all columns into a string

Is it possible to concatenate all columns of a table, including their column names, in a string?

The second (unmarked answer) in this question is very interesting and might work with further changes (maybe to join INFORMATION_SCHEMA.COLUMNS).

If the table has 3 columns (id, name, amount), the output should be:

id=1;name=something;amount=100

The statement should be generic because tables will have different number of columns.

Upvotes: 2

Views: 5401

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

This should do it for you:

DECLARE @query NVARCHAR(max)
DECLARE @columns NVARCHAR(max)
DECLARE @table NVARCHAR(max)

SET @table = 'tableName'

SELECT @columns = stuff((
            SELECT ', ''' + sc.NAME + '=''' + '+ CAST(' + sc.NAME + ' AS NVARCHAR(MAX))'
            FROM sys.columns sc
            INNER JOIN sys.tables st ON sc.object_id = st.object_id
            WHERE st.NAME = @table
            FOR XML path('')
            ), 1, 1, '')

SELECT @query = 'select ' + @columns + ' from ' + @table

EXEC sp_executesql @query

The code basically creates the code of a dynamic query, based on all the columns of the table you want to execute this for.

To see exactly what query will execute you can add a:

SELECT @query before the EXEC command and this will print the code.

After creating the query, it just executes it in the last part, EXEC sp_executesql.

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82474

If it's only 3 columns, I would do it hard coded in the sql:

SELECT 'id='+ ISNULL(CAST(id as varchar(10)), '') + 
       ';name='+ ISNULL(name, '') + 
       ';amount='+ ISNULL(CAST(amount as varchar(10)), '')
FROM table
WHERE Condition

My reasoning for a hard coded solution vs a generic one is performance and readability of the code. If you had 10 or more columns I would problaby join with sys.Columns table and do it more generic.

Upvotes: 0

Related Questions