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

Last Reply one year ago By pandeyism

Posted one year 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 one year ago.
Posted one year ago Modified on one year ago

Hi telldurges,

Refer below sample query.


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
      FROM @Test t
      WHERE t.libid = t1.libid
      FOR XMl PATH('')
Bookname = STUFF  
      SELECT DISTINCT ', '+ CAST(t.Bookname AS VARCHAR(MAX))  
      FROM @Test t
      WHERE t.libid = t1.libid
      FOR XMl PATH('')
FROM @Test t1  
GROUP BY libid ,Libname


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