multiple row data from database into single row in gridview

Question

In c# asp.net  how to display multiple row data from database in to a single row in gridview eg: Output which i want it to be: StaffName | Role | GroupNumber Adrian | Lecturer | 12 Instead of: StaffName | Role | GroupNumber Adrian | Lecturer | 1 Adrian | Lecturer | 2

Jaganathan Bantheswaran · Answer

You can use XML PATH to do it. Try like this SELECT p1.StaffName p1.Role ( SELECT GroupNumber + ' ' FROM YourTable p2 WHERE p2.ID = p1.ID ORDER BY GroupNumber FOR XML PATH('') ) AS GroupNumber FROM YourTable p1 GROUP BY p1.StaffName p1.Role;