Query explanation:
Consider a database table holding names, with three rows:
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary
?
Answer #1:
If you are on SQL Server 2017 or Azure, then go to this 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 ('')
) [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 ('')
), 2, 1000) [Students]
FROM dbo.Students ST2
Answer #2:
SQL Server 2017+ and SQL Azure: STRING_AGG
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
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
With grouping:
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
With grouping and sub-sorting
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Answer #3:
This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.
Use COALESCE
:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
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:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
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]).
Answer #4:
One method not yet shown via the XML
data()
command in SQL Server is:
Assume a table called NameList with one column called FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
returns:
"Peter, Paul, Mary, "
Only the extra comma must be dealt with.
As adopted from @NReilingh’s comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
Answer #5:
In SQL Server 2005
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
In SQL Server 2016
you can use the FOR JSON syntax
i.e.
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
And the result will become
Id Emails
1 abc@gmail.com
2 NULL
3 def@gmail.com, xyz@gmail.com
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 separator.
Answer #6:
Use COALESCE – Learn more from here
For an example:
102
103
104
Then write the below code in SQL Server,
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
The output would be:
102,103,104
Answer #7:
PostgreSQL arrays are awesome. Example:
Create some test data:
postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
name
-------
Peter
Paul
Mary
(3 rows)
Aggregate them in an array:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Convert the array to a comma-delimited string:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
DONE
Since PostgreSQL 9.0 it is even easier, quoting from deleted answer by “horse with no name”:
select string_agg(name, ',')
from names;
Hope you learned something from this post.
Follow Programming Articles for more!