Multiple Rows to One Comma-Separated Value Different Id

Multiple rows to one comma-separated value different ID

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

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 name , STUFF((SELECT '; ' + facilty 
                     FROM leads  
                     FOR XML PATH('')
                    ),1,2,'') as facilty, address 
from leads
where name is not null;

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);


SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))