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

Last Reply 2 months ago By pandeyism

Posted 2 months 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 2 months ago.
Posted 2 months ago Modified on 2 months 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