How to Get Multiple Rows into One Line as a String

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

SQL Server: combining multiple rows into one row

There are several methods.

If you want just the consolidated string value returned, this is a good quick and easy approach

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
    AND ISSUE = 19602

SELECT @combinedString as StringValue 

Which will return your combined string.

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534
    AND ISSUE = 19602

How to show data from multiple rows into single row as comma separated string

Modification:
If your SQL Server version is 2017 or above then you can use string_agg() to achieve your desired result. It's way too much faster than stuff() with For XML PATH()

with cte as
(SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
    INNER JOIN Books ON Books.Id = Books_Categories.BookId
    INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)

SELECT t.BookName,STRING_AGG(CategoryName,',')  CategoryName          
FROM cte t
GROUP BY t.BookName

You can achieve this in sql-server older than 2017 by using STUFF() with For XML PATH

Schema:

 CREATE TABLE [dbo].[books](
    [id] [int] NULL,
    [name] [varchar](50) NULL
 ) ON [PRIMARY]
 
 CREATE TABLE [dbo].[categories](
    [id] [int] NULL,
    [name] [varchar](50) NULL
 ) ON [PRIMARY]
 
 CREATE TABLE [dbo].[Books_Categories](
    [id] [int] NULL,
    [bookid] [int] NULL,
    [categoryid] [int] NULL
 ) ON [PRIMARY]

Insert statements:
insert into books values(1,'BookA');
insert into books values(2,'BookB');

 insert into categories values(1,'CategoryA');
 insert into categories values(2,'CategoryB');
 
 insert into Books_Categories values(1, 1,  1);
 insert into Books_Categories values(2, 1,  2);
 insert into Books_Categories values(3, 2,  2);

Query#1 (STUFF() and For XML PATH():

 with cte as
 (SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
     INNER JOIN Books ON Books.Id = Books_Categories.BookId
     INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
 
 SELECT t.BookName
       , STUFF(( SELECT ', ' + CategoryName
                 FROM cte 
                 WHERE BookName = t.BookName
                 FOR XML PATH(''),TYPE)
                 .value('.','NVARCHAR(MAX)'),1,2,'') AS CategoryName
 FROM cte t
 GROUP BY t.BookName
 GO

Query#2 (using string_agg() for SQL Server 2017(14x) and later):

 with cte as
 (SELECT Books.Name BookName, Categories.Name CategoryName FROM Books_Categories 
     INNER JOIN Books ON Books.Id = Books_Categories.BookId
     INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId)
 
 select t.bookname,string_agg(categoryname,',') CategoryName from cte t 
 GROUP BY t.BookName

Output:

SELECT
    ID, Name, 
    Emails = STUFF((
        SELECT ', ' + Email FROM Table2 WHERE Table2.ID = Table1.ID
        FOR XML PATH ('')),1,2,'')
FROM Table1

df7['Tweet'] = df7['Tweet'].str.replace('\n', ' ')

var result = users.GroupBy(u => u.Id)  // group by Id
                  .Select(g => new     // select values
                  { 
                      Id = g.Key, 
                      Name = g.First().Name, 
                      Hobbies = string.Join(", ", g.Select(u => u.Hobby)) 
                  })
                  .ToList();

 .Select(g => new Person
  { 
      Id = g.Key, 
      Name = g.First().Name, 
      Hobby = string.Join(", ", g.Select(u => u.Hobby)) 
  })

var result = users.GroupBy(u => u.Id)  // group by Id
                  .Select(g => new     // select values
                  { 
                      Id = g.Key, 
                      Name = g.First().Name, 
                      Hobbies = g.Select(u => u.Hobby).ToList() 
                  })
                  .ToList();

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;

SELECT
       Radi.Field,
       (SELECT
               a.AccessoryId+','
        FROM
               FieldAcce fa
               INNER JOIN Radi r ON fa.RadiSer = r.RadiSer
               INNER JOIN Acce a ON fa.AcceSer = a.AcceSer
        WHERE
               r.Id LIKE UPPER (@RNO)
               AND r.CourseID LIKE @CourseID
               AND r.PhaseId LIKE @PhaseID
               -- add the following 2 lines
               AND r.Field = Radi.Field
               AND a.AccessoryId = Acce.AccessoryId
        ORDER BY
               r.Field
        FOR XML PATH('')) AS [Accessory]
FROM
        FieldAcce
        INNER JOIN Radi ON FieldAcce.RadiSer = Radi.RadiSer
        INNER JOIN Acce ON FieldAcce.AcceSer = Acce.AcceSer
WHERE
        Radi.Id LIKE UPPER (@RNO)
        AND Radi.CourseID LIKE @CourseID
        AND Radi.PhaseId LIKE @PhaseID
GROUP BY
        Radi.Field,
        Acce.AccessoryId
ORDER BY
        Radi.Field

SELECT LISTAGG(emp, ' | ')
     WITHIN GROUP (ORDER BY emp) "Emp",
       MAX(date) "Date",
       MAX(route) "Route"
FROM information
WHERE date=Trunc(Sysdate);