sql server - how can achieve the same result through sql query -

i have table tblempbatch

username  filename  batchid           f1      1  b          f2      1  c          f3      2    d          f4      2 

i want output like

username  filename  batchid  a,b       f1,f2      1  c,d       f3,f4      2 

how can write sql query this.

try this: (to use in table change table name needed , check column name)

create table #table2(username varchar(10), filename varchar(10), batchid int)  insert #table2 values('a' , 'f1' , 1) insert #table2 values('b' , 'f2' , 1) insert #table2 values('c' , 'f3' , 2) insert #table2 values('d' , 'f4' , 2)  select     username =          stuff((             select                 ', ' + t2.username             #table2 t2                             t2.batchid = t1.batchid             group t2.username             xml path(''), type).value('.', 'varchar(max)'         ), 1, 2, ''),     filename =              stuff((                 select                     ', ' + t2.filename                 #table2 t2                                     t2.batchid = t1.batchid                 group t2.filename                 xml path(''), type).value('.', 'varchar(max)'             ), 1, 2, ''), batchid #table2 t1 group batchid