Concatenate multiple Rows into single Row with comma separated string in SQL Server

Last Reply 11 days ago By pandeyism

Posted 11 days ago

hello sir

How to concatenate string multiple row in a single row in sql server

i have a query in sql but facing problem

please see it and help us.

Bookid Bookname libid libname
334 physics 1 LIB1
336 math 1 LIB1
6468 science 1 LIB1
357 hindi 3 LIB3
358 eng 3 LIB3
 
i want to show  
libid Bookid Libname Bookname
1 334,366,6468 LIB1 physics,math,science
3 357,358 LIB3 Hindi,english
You are viewing reply posted by: pandeyism 11 days ago.
Posted 11 days ago Modified on 11 days ago

Hi telldurges,

Refer below sample query.

SQL

DECLARE @Test AS TABLE(Bookid INT, Bookname VARCHAR(MAX), libid INT, libname VARCHAR(MAX))
INSERT INTO @Test VALUES(334,'physics',1,'LIB1')
INSERT INTO @Test VALUES(336,'math',1,'LIB1')
INSERT INTO @Test VALUES(6468,'science',1,'LIB1')
INSERT INTO @Test VALUES(357,'hindi',3,'LIB3')
INSERT INTO @Test VALUES(358,'eng',3,'LIB3')

SELECT libid, Bookid = STUFF
(
    (  
      SELECT DISTINCT ', '+ CAST(t.Bookid AS VARCHAR(MAX))  
      FROM @Test t
      WHERE t.libid = t1.libid
      FOR XMl PATH('')
    ),1,1,''  
),Libname,
Bookname = STUFF  
(  
    (  
      SELECT DISTINCT ', '+ CAST(t.Bookname AS VARCHAR(MAX))  
      FROM @Test t
      WHERE t.libid = t1.libid
      FOR XMl PATH('')
    ),1,1,''  
)  
FROM @Test t1  
GROUP BY libid ,Libname

Output

libid Bookid Libname Bookname
1 334, 336, 6468 LIB1 math, physics, science
3 357, 358 LIB3 eng, hindi