How to Concat Multiple Rows into One Column in SQL Server

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

How to concatenate multiple rows into one field in sql server

SQL Server doesn't have great support for aggregate string concatenation. But you can do:

select stuff((select ', ' + hobbies
              from peoples_hobbies
              where person_id = 5
              for xml path ('')
             ), 1, 2, '') as hobbies;

How to concatenate many rows with same id in sql?

In SQL-Server you can do it in the following:

QUERY

SELECT id, displayname = 
    STUFF((SELECT DISTINCT ', ' + displayname
           FROM #t b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id

TEST DATA

create table #t 
(
id int,
displayname nvarchar(max)
)

insert into #t values    
 (1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')

OUTPUT

id  displayname
1   Editor, Reviewer
7   Editor, EIC, Reviewer
19  Editor, EIC, Reviewer

Merge multiple rows into one column without duplicates

For SQL Server you can use:

select player,
  stuff((SELECT distinct ', ' + cast(score as varchar(10))
           FROM yourtable t2
           where t2.player = t1.player
           FOR XML PATH('')),1,1,'') 
from yourtable t1
group by player

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

Combine multiple rows into single row in SQL View (without group by or with CTE?)

Your "magic function" would appear to be STRING_AGG().

The code would then look like this:

CREATE VIEW MyView AS 
    SELECT Staff.Id,
           STRING_AGG(CONCAT(OtherStaff.Name, ', ', OtherStaff.Value), '; ')  WITHIN GROUP (ORDER BY OtherStaff.Name),
           SomeTable.SomeVal   
    FROM dbo.[Staff] Staff JOIN
         dbo.[OtherStaff] OtherStaff
         ON OtherStaff.StaffId = Staff.Id JOIN
         dbo.[SomeTable] SomeTable
         ON SomeTable.StaffId = Staff.Id
    GROUP BY Staff.Id, SomeTable.SomeVal;

Listing unaggregated columns in both the GROUP BY and SELECT should not be too troublesome. After all, you can just cut-and-paste them.

Merging multiple rows with same column in SQL Server

Use This

SELECT category,segment,payment = STUFF((
                                SELECT '+' + CONVERT(VARCHAR,payment)
                                FROM TABLENAME t
                                WHERE t.category = TABLENAME.category AND t.segment = TABLENAME.segment                               
                                FOR XML PATH('')
                                ), 1, 1, '')
FROM TABLENAME 
GROUP BY category,segment

if you get payment with + sign then use stuff like this.

SELECT category,segment,SUM(payment) as payment
FROM TABLENAME 
GROUP BY category,segment

if you want sum then remove stuff and use Sum(payment) only.

---

Related Topics