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 |
