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>
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')
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
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
.
How to concatenate all columns values in SQL Server 2005?
Here is a github project for a solution to autogenerating insert statements for tables.
concatenate all columns from with names of columns also in it, one string for every row
Since you are on 2019, string_agg() with a bit if JSON
Example
Select NewVal
From MyTable A
Cross Apply ( Select NewVal = string_agg([key]+'='+isnull(value,'null'),'|')
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES ))
) B
Results
NewVal
COL1=1|COL2=c2r1|COL3=null
COL1=2|COL2=c2r2|COL3=2.335000000000000e+000 -- Don't like the float
EDIT to Trap FLOATs
Select NewVal
From MyTable A
Cross Apply ( Select NewVal = string_agg([key]+'='+isnull(case when value like '%0e+0%' then concat('',convert(decimal(15,3),convert(float,value))) else value end,'null'),'|')
From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES ))
) B
Results
NewVal
COL1=1|COL2=c2r1|COL3=null
COL1=2|COL2=c2r2|COL3=2.335
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
MySQL concatenating all columns
To concatenate all columns in a table, you can't use the *
keyword, but you need to explicitly list all columns:
SELECT CONCAT(col1, col2, col3, ....)
FROM yourtable
or you might want to use CONCAT_WS
that will skip null values:
SELECT CONCAT_WS(',', col1, col2, col3, ....)
FROM yourtable
If you don't want to specify all column names manually, you could use a dinamic query. This query will return all column names of your table:
SELECT `column_name`
FROM `information_schema`.`columns`
WHERE `table_schema`=DATABASE()
AND `table_name`='yourtable';
and using GROUP_CONCAT you can obtain a list of all column names:
GROUP_CONCAT(CONCAT('`', column_name, '`'))
quoted, in a comma separated format:
`col1`,`col2`,`col3`,`col4`,...
so now we have all the elements to create our query dinamically:
SELECT
CONCAT(
'SELECT CONCAT_WS(\'\',',
GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
') AS all_columns FROM yourtable;')
FROM `information_schema`.`columns`
WHERE `table_schema`=DATABASE()
AND `table_name`='yourtable'
INTO @sql;
this query will set the @sql string to something like:
SELECT CONCAT_WS('', col1, col2, col3, ....) AS all_columns FROM yourtable
and this code will execute it:
PREPARE stmt FROM @sql;
EXECUTE stmt;
Please see fiddle here.
Generic select concatenation all columns from a table
DECLARE @Names VARCHAR(8000)
DECLARE @SQLQuery AS NVARCHAR(500)
SELECT @Names = COALESCE(@Names + ', ', '') + name
FROM sys.columns WHERE object_id = OBJECT_ID('tablename')
SET @SQLQuery = 'SELECT CONCAT(' + @Names + ') FROM tablename '
EXECUTE(@SQLQuery)
Related Topics
Sqlite Database - Select the Data Between Two Dates
Is Natural (Join) Considered Harmful in Production Environment
Split Function by Comma in SQL Server 2008
Find Referenced Field(S) of Foreign Key Constraint
What Are the Uses for Cross Join
How to Simulate Deadlock on SQL Server
SQL Function as Default Parameter Value
SQL Server Convert Select a Column and Convert It to a String
Using a Conditional Update Statement in SQL
Using an Alias Column in the Where Clause in Ms-SQL 2000
Get All Dates in Date Range in SQL Server
Postgres Interval Using Value from Table
How to Sort in Order as Entered in SQL Server
Where Should I Start with My Opc-Ua Client
In Postgresql, Force Unique on Combination of Two Columns