How To Concatenate this Sql Column into Single cell?

Question

ClassName    BookCode
First          2
Second         2
Third          2
First          3
Fourth         3
Fifth          3

I want to concatenate this into like this :

ClassName             BookCode
First,Second,Third       2
First,Fourth,Fifth       3

Answer 1

Try this

SELECT 
Distinct BookCode,
    (
        SELECT ClassName +','
        FROM table1 t2
        WHERE t2.BookCode= t1.BookCode
        FOR XML PATH('')
    )   Concatenated
    
FROM table1 t1

Output:

WORKING FIDDLE

Answer 2

try this query:

select distinct t1.BookCode,
  STUFF((SELECT distinct ',' + t2.ClassName
         from yourtable t2
         where t1.BookCode = t2.BookCode
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,0,',') ClassName
from yourtable t1;

NOT TESTED.