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
Concatenate many rows into a single text string with grouping
try this -
SELECT DISTINCT
fileid
, STUFF((
SELECT N', ' + CAST([filename] AS VARCHAR(255))
FROM tblFile f2
WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1
Concatenate many rows into a single text string with group
SELECT VENDOR_NAME,
DIRECTORY_NAME = STUFF(
(
SELECT ', '+DIRECTORY_NAME AS [text()]
FROM _table AS t
WHERE t.VENDOR_NAME = tt.VENDOR_NAME
ORDER BY t.DIRECTORY_NAME
FOR XML PATH('')
), 1, 2, '')
FROM _table tt
GROUP BY VENDOR_NAME;
Concatenate multiple result rows of one column into one, group by another column
Simpler with the aggregate function string_agg()
(Postgres 9.0 or later):
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;
The 1
in GROUP BY 1
is a positional reference and a shortcut for GROUP BY movie
in this case.
string_agg()
expects data type text
as input. Other types need to be cast explicitly (actor::text
) - unless an implicit cast to text
is defined - which is the case for all other string types (varchar
, character
, name
, ...) and some other types.
As isapir commented, you can add an ORDER BY
clause in the aggregate call to get a sorted list - should you need that. Like:
SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM tbl
GROUP BY 1;
But it's typically faster to sort rows in a subquery. See:
- Create array in SELECT
Can I concatenate multiple MySQL rows into one field?
You can use GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH
You need a correlated subquery:
SELECT hs.department AS department,
STUFF( (SELECT ', ' + top_employee
FROM findhighest_secondstep hs2
WHERE hs2.department = hs.department
FOR XML PATH('')
), 1, 2, ''
) as top_employees
FROM findhighest_secondstep hs
GROUP BY hs.department
How to concatenate text from multiple rows into a single text string in Oracle server?
Have you tried this?
select end_date,
listagg(CLOSE_DATE, ',') within group (order by CLOSE_DATE DESC) as close_dates,
listagg(id, ',') within group (order by id) as ids
from TBL_S_PLCLOSEDATE
where D_END = date '2018-05-18'
group by end_date;
Related Topics
Add Row to Query Result Using Select
Select Where Count of One Field Is Greater Than One
Get All Characters Before Space in MySQL
SQL Row_Number() Function in Where Clause Without Order By
Generating Random Number in Each Row in Oracle Query
Query Across Multiple Databases on Same Server
Delphi - Prevent Against SQL Injection
Use String Contains Function in Oracle SQL Query
Sql: Search for a String in Every Varchar Column in a Database
Sum Columns with Null Values in Oracle
Are Determinants and Candidate Keys Same or Different Things
SQL Server Sub Query with a Comma Separated Resultset
Is Order in a Subquery Guaranteed to Be Preserved
Counting Number of Records Hour by Hour Between Two Dates in Oracle
How to Write a Function in the H2 Database Without Using Java
How to Use Regular Expression in SQL Server
Does a Select Query Always Return Rows in the Same Order? Table with Clustered Index