SELECT
Staff,
(SELECT '( ' + Convert(char(7),[Schdate],100 )+ ' ' + [Course] + '-S' + [Session] + ')'
FROM Tb_Sch_Time_Table_Details sch
where sch.Staff= sch1.Staff
FOR XML PATH('')) [Course]
FROM Tb_Sch_Time_Table_Details sch1
GROUP BY Staff
ORDER BY
When i run the above query output as follows
Staff Course
CPG ( Aug 26 B Tech 1-S3)( Aug 26 B Tech 1-S4)( Aug 28 ETO-S3)( Aug 28 ETO-S4)
But i want the correct output as follows
Staff Course
CPG Aug26(B Tech 1-S3,B Tech 1-S4) Aug28(ETO -S3,ETO -S4)
for that getting a output in my query what changes i have to made please help me