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

Question

I would like to ask how to show one row only in same id, in below example, I just want to show the categories of a book in one row

Table     Books                 Categories                 Books_Categories
   
      BookId | Name        CategoryId |    Name       Id  | BookId | CategoryId
      --------------       ----------------------     -------------------------
       1    | BookA         1        | CategoryA      1  |   1    |    1
       2    | BookB         2        | CategoryB      2  |   1    |    2
                                                      3  |   2    |    2 

SELECT Books.Name, Categories.Name FROM Books_Categories 
    INNER JOIN Books ON Books.Id = Books_Categories.BookId
    INNER JOIN Categories ON Categories.Id = Books_Categories.CategoryId

Query Result
    
    BookA  | CategoryA       
    BookA  | CategoryB       
    BookB  | CategoryA

How can I show the result like this

BookA | CategoryA, CategoryB
BookB | CategoryA

Answer 1

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:

BookName	CategoryName
BookA	CategoryA, CategoryB
BookB	CategoryB

db<fiddle here