Concatenate Many Rows into a Single Text String with Grouping

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



Leave a reply



Submit