Concat All Column Values in SQL

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

How to concatenate all the records in a column returned by a query into one varchar string in T-SQL?

declare @s varchar(8000)
select @s = coalesce(@s + ', ' + col, col) from tbl

How to concatenate all columns in a select with SQL Server

Any number of columns for a given tablename;
If you need column names wrapped with <text>

DECLARE @s VARCHAR(500)

SELECT @s =  ISNULL(@s+', ','') + c.name   
FROM  sys.all_columns c join sys.tables  t 
            ON  c.object_id = t.object_id
WHERE t.name = 'YourTableName'

SELECT '<text>' + @s + '</text>'

SQL Fiddle Example here

-- RESULTS 
<text>col1, col2, col3,...</text>

If you need select query result set wrapped with <text> then;

SELECT @S =  ISNULL( @S+ ')' +'+'',''+ ','') + 'convert(varchar(50), ' + c.name    FROM 
       sys.all_columns c join sys.tables  t 
       ON  c.object_id = t.object_id
WHERE t.name = 'YourTableName'

EXEC( 'SELECT ''<text>''+' + @s + ')+' + '''</text>'' FROM YourTableName')

SQL Fiddle Example here

--RESULTS
<text>c1r1,c2r1,c3r1,...</text>
<text>c1r2,c2r2,c3r2,...</text>
<text>c1r3,c2r3,c3r3,...</text>

How to concat all columns of a table with different types with their values by identity in SQL query?

As discussed with comment,UDF is not work because the below query deals with dynamic SQL. Although I am not recommending this solution for longer run as this involves much dynamic queries.

The code is self explanatory. First get the columns converting into NVARCHAR and columns list. Then get the data unpivoting Column Name and Value. Then concatenate the result as desired.

Create a procedure like this,

CREATE PROC uspGetLogString(@TABLE VARCHAR(50), @ID INT)
AS
    BEGIN
    DECLARE @RETURNVALUE NVARCHAR(MAX) = ''
    DECLARE @WHERECLAUSE NVARCHAR(MAX) = 'ID = ' + CONVERT(NVARCHAR(50), @ID)
    DECLARE @COLUMNSWITHTYPE NVARCHAR(MAX) = ''
    DECLARE @COLUMNS NVARCHAR(MAX) = ''

    SELECT @COLUMNSWITHTYPE  = @COLUMNSWITHTYPE + 
        CASE 
            WHEN CL.DATA_TYPE = 'BIT' THEN 'CONVERT(NVARCHAR(50), CASE WHEN ' + CL.COLUMN_NAME + ' = 1 THEN ''True'' ELSE ''False'' END) AS ' + CL.COLUMN_NAME + ''
            WHEN CL.DATA_TYPE != 'NVARCHAR' THEN 'CONVERT(NVARCHAR(50), ' + CL.COLUMN_NAME + ') AS ' + CL.COLUMN_NAME + '' 
            ELSE CL.COLUMN_NAME 
        END  + ', ' FROM INFORMATION_SCHEMA.COLUMNS CL WHERE TABLE_NAME = @TABLE
    SELECT @COLUMNS  = @COLUMNS + CL.COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS CL WHERE TABLE_NAME = @TABLE

    SELECT @COLUMNSWITHTYPE = LEFT(@COLUMNSWITHTYPE, LEN(@COLUMNSWITHTYPE) - 1)
    SELECT @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)
    SELECT @COLUMNSWITHTYPE = 'SELECT ' + @COLUMNSWITHTYPE + ' FROM ' + @TABLE + ' WHERE ' + @WHERECLAUSE

    DECLARE @DynamicSQL NVARCHAR(MAX) = ''

    SELECT @DynamicSQL = 'DECLARE @RESULT NVARCHAR(MAX) = ''''
    SELECT @RESULT = @RESULT + TAB.COLUMN_NAME + '':'' + U.ColumnValue +'', ''
    FROM INFORMATION_SCHEMA.COLUMNS TAB
    INNER JOIN
        (
            SELECT ColumnName, ColumnValue
            FROM (' +  @COLUMNSWITHTYPE + ')
            AS P
            UNPIVOT
            (
                ColumnValue FOR ColumnName IN (' + @COLUMNS + ' )
            ) as unpvt 
        )
         as U ON U.ColumnName = Tab.COLUMN_NAME
    WHERE TAB.TABLE_NAME = ''' +  @TABLE + ''';
    SELECT @RESULT = LEFT(@RESULT, LEN(@RESULT) - 1)
    SELECT @RESULT
    '
    EXEC SP_EXECUTESQL @DynamicSQL, N'@OUTPUT NVARCHAR(MAX) OUT', @RETURNVALUE
END
GO

Then call it like,

EXEC dbo.uspGetLogString 'EMPLOYEE', 1

How to concat all values of single column in mysql

You could use a combination of CONCAT and GROUP_CONCAT

Query

SELECT GROUP_CONCAT(CONCAT('"', A, '"')) AS `combined_A`
FROM `your_table_name`;

And if you want to remove the duplicates. Then use DISTINCT with GROUP_CONCAT.

Query

SELECT GROUP_CONCAT(DISTINCT CONCAT('"', `A`, '"')) AS `combined_A`
FROM `your_table_name`;

SQL Fiddle demo

How to concatenate all columns values in SQL Server 2005?

Here is a github project for a solution to autogenerating insert statements for tables.

Dynamically concatenate all columns in table

You could just add some pease of code into your example:
CHAR(39) + c.name + CHAR(39) + ' +

So the final code will look like this:

    create table test1(id int, name varchar(10))
    insert into test1 values(1,'Tom'),(2,'John'),(3,'Alice')
    select * from test1

    declare @s varchar(max)
    SELECT @s =  
        ISNULL( @s+ ')' +'+'',''+ ','') + CHAR(39) + c.name + CHAR(39) + ' + convert(varchar(50), ' + c.name    
    FROM  sys.all_columns c join sys.tables  t 
           ON  c.object_id = t.object_id
    WHERE t.name = 'test1'

    EXEC( 'SELECT '' ''+' + @s + ')+' + ''' '' FROM test1')

Concatenate all columns into a string

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.

---

Related Topics