MS-SQL Combining multiple rows, columns into a single record?

Question

MS-SQL,..

Let's suppose I have a table called dbo.students with fields like

SubjectID StudentfName StudentsName ---------- ------------- ------------ 1 Mary Abc 1 John Defs 1 Sam Ghix 2 Alaina Jklxx 2 Edward Mnoqwww

Result I expect is this:

SubjectID StudentName ---------- ------------- 1 Mary Abc, John Defs, Sam Ghix 2 Alaina Jklxx, Edward Mnoqwww

I know how to combine the StudentfName and StudentsName as StudentName, though I want to combine all the names in a single row using the unique value from the SubjectID?

Aaron Bertrand explains all options in [SQL Server Gruped Concatenation](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation). I use a SQLCLR Aggregate because it's the fastest and easiest to use. — Panagiotis Kanavos, May 05 '17 at 09:44
Possible duplicate of [string\_agg for sql server pre 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017) — , Jun 14 '18 at 12:43

Kannan Kandasamy · Answer 1 · 2017-05-05T09:19:11.230

0

If it is sql server VNext >= 2017 then you can use string_agg function

select subjectid, string_agg(studentName,', ') from yourtable
    group by subjectid

For earlier versions you require to use stuff and custom code as below:

select subjectid,  stuff(( select concat( ',', studentfname, ' ', studentsname) from #yourstudent y where y.subjectid = u.subjectid for xml path('')),1,1, '') 
    from #yourstudent u
    group by subjectid

Your table input data:

create table #yourstudent (subjectid int, studentfname varchar(10), studentsname varchar(10))

insert into #yourstudent (subjectid, studentfname, studentsname) values
 ( 1       ,'Mary','Abc')
,( 1       ,'John','Defs')
,( 1       ,'Sam','Ghix')
,( 2       ,'Alaina','Jklxx  ')
,( 2       ,'Edward','Mnoqwww')

edited May 05 '17 at 09:19

answered May 05 '17 at 09:07

Kannan Kandasamy

13,405
3
25
38

`XML PATH` by itself will fail if the text contains `&`, `<` or any other characters that have to be HTML encoded when they are converted to XML – Panagiotis Kanavos May 05 '17 at 09:44
It is name, i dont think they will have special characters... If it is question poster might provided sample data... – Kannan Kandasamy May 05 '17 at 09:47
You mean like `O'Reilly`? The use of `XML PATH` for string aggregation is is described in Aaron Bertrand's articles which explain this very problem. He also explains why he *doesn't* uses `varchar` since the table may contain non-latin text. – Panagiotis Kanavos May 05 '17 at 09:52

score 0 · Answer 2 · answered May 05 '17 at 09:17

0

Hope this will work,

select
    SubjectID,
    stuff((
        select ',' + t.[StudentfName]+ ' ' +t.[StudentsName]
        from #your_table t
        where t.SubjectID = t1.SubjectID
        order by t.SubjectID
        for xml path('')
    ),1,1,'') as StudentName
from #your_table t1
group by SubjectID;

answered May 05 '17 at 09:17

PP006

681
7
17

Might as well explain what that does and where it came from – Panagiotis Kanavos May 05 '17 at 09:17
Thanks, this works for me based on the example,.. concat fails, unfortunately have SQL 2008 r2 to work with. – Jim Tsetsos May 07 '17 at 00:50
When I use the GROUP BY on ID (in my situation, different tables for my application) I get an error on a field I have selected, if I add the filed to the group by then the next field in the select fails (and so on) with the same error: Column 'dbo.AB_AB_ABC.State' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Jim Tsetsos May 07 '17 at 00:58

score 0 · Answer 3 · answered May 05 '17 at 09:27

;WITH cte(SubjectID,StudentfName,StudentsName)
AS
(

SELECT 1,'Mary'  ,'Abc'     Union all
SELECT 1,'John'  ,'Defs'    Union all
SELECT 1,'Sam'   ,'Ghix'    Union all
SELECT 2,'Alaina','Jklxx'   Union all
SELECT 2,'Edward','Mnoqwww'
)
SELECT   SubjectID ,(STUFF((SELECT DISTINCT ', ' + CONCAT(CAST( StudentfName AS VARCHAR(50)), ',' ,CAST(StudentsName AS VARCHAR(50)))
        FROM cte i  WHERE i.SubjectID =u.SubjectID FOR XML PATH('')),1,1,'')) AS Studentfullname
From cte u
group by SubjectID

Panagiotis Kanavos · Answer 4 · 2017-05-05T09:43:19.477

This is called String aggregation or Grouped concatenation.

There are multiple techniques. All are described in Aaron Bertrand's articles, eg Grouped Concatenation in SQL Server.

The fastest and simplest to use is to create and use a SQLCLR Aggregate. Using GROUPED_CONCAT_S as Aaron Bertrand does:

SELECT 
   SubjectID, 
   dbo.GROUP_CONCAT_S(StudentfName + ' ' + StudentsName, 1)
FROM dbo.Students
GROUP BY SubjectID
ORDER BY SubjectID;

Writing and deploying the SQLCLR aggregate requires a few steps, but the memory, performance and usability benefits are significant.

SQL Server 2017 will offer a native STRING_AGG that will be faster even than SQLCLR, eg:

SELECT 
   SubjectID, 
   STRING_AGG(StudentfName + ' ' + StudentsName, 1)
FROM dbo.Students
GROUP BY SubjectID
ORDER BY SubjectID;

Next comes XML PATH. It's easier to type but not that easy to use. Essentially, you convert all fields to XML elements and combine them with an XPATH using an empty string as an element tag:

SELECT SubjectID, 
       Students = STUFF((SELECT N', ' + 
                         StudentfName + ' ' + StudentsName
                         FROM Students AS p2
                         WHERE p2.SubjectID = p.SubjectID 
                         ORDER BY StudentfName, StudentsName
                         FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)')
                      ,1, 2, N'')
FROM Students AS p
GROUP BY subjectid
ORDER BY subjectid;

Notice the PATH, TYPE).value(...) function. If the fields contained any characters that have special meaning for XML like ', < or &, they would get XML encoded, eg to >. ,TYPE).value() returns the element's value instead.

The other techniques aren't really used because they are more cumbersome and far slower.

MS-SQL Combining multiple rows, columns into a single record?

4 Answers4