Recently I found myself needing to aggregate my multiple row SQL data into a single row. This was because the parent data was more useful to me than the multiple row’d data, but I still needed to include it because it was still an essential part of my report. There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE. cc lang=”sql” DECLARE @ColumnList varchar(8000).
Consider a database table holding names, with three rows:
Is there an easy way to turn this into a single string of
Steve ChambersPeter, Paul, Mary
?22.8k12 gold badges103 silver badges147 bronze badges
JohnnyMJohnnyM11.4k10 gold badges30 silver badges35 bronze badges
45 Answers
12 next
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
Result I expected was:
I used the following
T-SQL
: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: Ritesh
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use
COALESCE
:Just some explanation (since this answer seems to get relatively regular views):
- Coalesce is really just a helpful cheat that accomplishes two things:
1) No need to initialize
@Names
with an empty string value. 2) No need to strip off an extra separator at the end.
- The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make
@Names
NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:
or:
Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).
Martin Smith357k61 gold badges601 silver badges710 bronze badges
Chris ShafferChris Shaffer28.7k4 gold badges41 silver badges60 bronze badges
One method not yet shown via the
XML
data()
command in MS SQL Server is:Assume table called NameList with one column called FName,
returns:
Only the extra comma must be dealt with.
Edit: As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
user147738813.6k21 gold badges103 silver badges206 bronze badges
jens frandsenjens frandsen3,3691 gold badge10 silver badges2 bronze badges
Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.
Without grouping
With grouping :
With grouping and sub-sorting
Mathieu RendaMathieu Renda5,0481 gold badge16 silver badges28 bronze badges
In SQL Server 2005
In SQL Server 2016
you can use the FOR JSON syntax
i.e.
And the result will become
This will work even your data contains invalid XML characters
the
'},{'_':'
is safe because if you data contain '},{'_':',
it will be escaped to '},{'_':'
You can replace
', '
with any string separatorAnd in SQL Server 2017, Azure SQL Database
You can use the new STRING_AGG function
In MySQL there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:
Peter Mortensen14.2k19 gold badges88 silver badges114 bronze badges
Darryl HeinDarryl Hein70.8k84 gold badges193 silver badges246 bronze badges
Use COALESCE - Learn more from here
For an example:
102
103
104
Then write below code in sql server,
Output would be:
Graham4,05314 gold badges42 silver badges63 bronze badges
pedrampedram5,1866 gold badges42 silver badges67 bronze badges
Postgres arrays are awesome. Example:
Create some test data:
Aggregate them in an array:
Convert the array to a comma delimited string:
DONE
Since PostgreSQL 9.0 it is even easier.
Community♦
hgmnzhgmnz11.9k3 gold badges32 silver badges40 bronze badges
Oracle 11g Release 2 supports the LISTAGG function. Documentation here.
Warning
Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.
AlexAlex7,3338 gold badges58 silver badges75 bronze badges
In SQL Server 2005 and later, use the query below to concatenate the rows.
George Garchagudashvili5,41012 gold badges34 silver badges50 bronze badges
Yogesh BhadauiryaYogesh Bhadauirya
I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:
DanaDana20.4k16 gold badges53 silver badges71 bronze badges
You need to create a variable that will hold your final result and select into it, like so.
Tigerjz32Tigerjz322,7143 gold badges18 silver badges31 bronze badges
A recursive CTE solution was suggested, but no code provided. The code below is an example of a recursive CTE -- note that although the results match the question, the data doesn't quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. Changing it to match all rows in the table is left as an exercise for the reader.
jmorenojmoreno11.1k2 gold badges39 silver badges70 bronze badges
Starting with PostgreSQL 9.0 this is quite simple:
In versions before 9.0
a_horse_with_no_namea_horse_with_no_namearray_agg()
can be used as shown by hgmnz319k50 gold badges488 silver badges592 bronze badges
In SQL Server vNext this will be built in with the STRING_AGG function, read more about it here:https://msdn.microsoft.com/en-us/library/mt790580.aspx
Henrik FransasHenrik Fransas
Using XML helped me in getting rows separated with commas. For the extra comma we can use the replace function of SQL Server. Instead of adding a comma, use of the AS 'data()' will concatenate the rows with spaces, which later can be replaced with commas as the syntax written below.
Peter Mortensen14.2k19 gold badges88 silver badges114 bronze badges
DiwakarDiwakar
A ready-to-use solution, with no extra commas:
An empty list will result in NULL value.Usually you will insert the list into a table column or program variable: adjust the 255 max length to your need.
(Diwakar and Jens Frandsen provided good answers, but need improvement.)
Peter Mortensen14.2k19 gold badges88 silver badges114 bronze badges
Daniel ReisDaniel Reis10.1k5 gold badges37 silver badges66 bronze badges
Max SzczurekMax Szczurek3,5052 gold badges14 silver badges25 bronze badges
This puts the stray comma at the beginning.
However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).
You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because Google doesn't do work stuff at home :-)
Peter Mortensen14.2k19 gold badges88 silver badges114 bronze badges
gbngbn353k60 gold badges499 silver badges587 bronze badges
With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.
Below is an example that uses SQL Server 'Information_Schema.Columns' table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.
Mike Barlow - BarDevMike Barlow - BarDev7,28515 gold badges54 silver badges77 bronze badges
For Oracle DBs, see this question: How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
The best answer appears to be by @Emmanuel, using the built-in LISTAGG() function, available in Oracle 11g Release 2 and later.
as @user762952 pointed out, and according to Oracle's documentation http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php, the WM_CONCAT() function is also an option. It seems stable, but Oracle explicitly recommends against using it for any application SQL, so use at your own risk.
Other than that, you will have to write your own function; the Oracle document above has a guide on how to do that.
Community♦
ZeroKZeroK
I really liked elegancy of Dana's answer. Just wanted to make it complete.
6254 gold badges18 silver badges40 bronze badges
This answer will require some privilege in server to work.
Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one
If you want, I have already created the assembly, and it is possible to download the DLL here.
Once you have downloaded it, you will need to run the following script in your SQL Server:
Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:
Hope it helps!!!
NizamNizam3,4033 gold badges33 silver badges53 bronze badges
I usually use select like this to concatenate strings in SQL Server:
Vladimir NesterovskyVladimir Nesterovsky
If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one.
PramodPramod
MySQL complete Example:
We have Users which can have many Data's and we want to have an output, where we can see all users Datas in a list:
Result:
Table Setup:
Query:
user1767754user176775411k5 gold badges78 silver badges91 bronze badges
In Oracle, it is
Peter Mortensenwm_concat
. I believe this function is available in the 10g release and higher.14.2k19 gold badges88 silver badges114 bronze badges
user762952user762952
endo64endo64
This method applies to Teradata Aster database only as it utilizes its NPATH function.
Again, we have table Students
Then with NPATH it is just single SELECT:
Result:
topcheftopchef13.7k7 gold badges50 silver badges92 bronze badges
12 next
protected by Community♦Aug 2 '11 at 14:32
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
Would you like to answer one of these unanswered questions instead?