Combine Multiple Rows into One Space Separated String

Combine multiple rows into one space separated string

Use the GROUP_CONCAT aggregate function:

  SELECT yt.userid,
         GROUP_CONCAT(yt.col SEPARATOR ' ') AS combined
    FROM YOUR_TABLE yt
GROUP BY yt.userid

The default separator is a comma (","), so you need to specify the SEPARATOR of a single space to get the output you desire.

If you want to ensure the order of the values in the GROUP_CONCAT, use:

  SELECT yt.userid,
         GROUP_CONCAT(yt.col ORDER BY yt.col SEPARATOR ' ') AS combined
    FROM YOUR_TABLE yt
GROUP BY yt.userid

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 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:

print df.groupby('value')['tempx'].apply(' '.join).reset_index()
   value                                              tempx
0    1.5  picture1 picture555 picture255 picture365 pict...

df.groupby('ID').Name.apply(','.join)

ID
1                     a,b,c,d
2               er,get,better
3    hot,cold,warm,sweet,heat
Name: Name, dtype: object

df.groupby('ID').Name.apply(','.join).reset_index()

   ID                      Name
0   1                   a,b,c,d
1   2             er,get,better
2   3  hot,cold,warm,sweet,heat

df.assign(Name=df.Name.astype(str)).groupby('ID').Name.apply(','.join).reset_index()

   ID                      Name
0   1                   a,b,c,d
1   2             er,get,better
2   3  hot,cold,warm,sweet,heat

select
    a.[ImageID], 
    a.ImageFile,
    a.[ImageTitle],
    a.[ImageCaption],
    a.[Description],
    a.[Active],
    c.[ListingID],
    c.[Active],
    c.[LevelID],
    c.[Title],
    stuff
    (
        (
        select ' ' + CAST(TT.AlbumID AS VARCHAR(10))
        from BD_AlbumGallery as TT
        where TT.[ImageID] = A.[ImageID]
        for xml path(''), type
        ).value('data(.)', 'nvarchar(max)')
    , 1, 1, '') as Listings
from BD_Gallery as A
    left outer join BD_Listing as C on a.ListingID = c.ListingID
where
    a.ListingID = @passedListingID and
    a.Active = 1 and c.Active = 1 and
    c.LevelID > 5

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

SET group_concat_max_len = 2048;

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

>>> s.str.cat(sep=', ')
'I, will, hereby, am, gonna, going, far, to, do, this'

SQL> create table test (id, col) as
  2  select rownum, a.column_name
  3  from user_tab_columns a cross join user_tab_columns b
  4  cross join user_tab_columns c;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
      9261

SQL> select listagg(col, ' ') within group (order by null) result from test;
select listagg(col, ' ') within group (order by null) result from test
                                                                  *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

SQL> select length(xmlagg(xmlelement(e, col, ' ').extract('//text()') order by null).GetClobVal()) length_result
  2  from test;

LENGTH_RESULT
-------------
        51156

SQL>