SQL SERVER – Group by Rows and Columns using XML PATH – Efficient Concating Trick

I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title. Any way here is the question I received.

"I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.

For example here is my table:

How can I generate result as following?

"

Now you can see how easy the question is but so hard to come up with either solution or  title of this blog post. We can use XML PATH and come up with the solution where we combine two or more columns together and display desired result.

Here is the quick script which does the task ask, I have used temporary tables so you can just take this script and quickly run on your machine and see how it returns results.

Let me know if there are any better ways to do the same.

-- Create tableCREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), Instructor VARCHAR(100), RoomNo VARCHAR(100))GO-- Populate tableINSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)SELECT 'Mark', 'Algebra', 'Dr. James', '101'UNION ALLSELECT 'Mark', 'Maths', 'Dr. Jones', '201'UNION ALLSELECT 'Joe', 'Algebra', 'Dr. James', '101'UNION ALLSELECT 'Joe', 'Science', 'Dr. Ross', '301'UNION ALLSELECT 'Joe', 'Geography', 'Dr. Lisa', '401'UNION ALLSELECT 'Jenny', 'Algebra', 'Dr. James', '101'GO-- Check orginal dataSELECT *FROM #TestTableGO-- Group by Data using column and XML PATHSELECTStudentName,STUFF((SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))FROM #TestTableWHERE (StudentName = StudentCourses.StudentName)FOR XML PATH (''))
,1,2,'') AS NameValuesFROM #TestTable StudentCoursesGROUP BY StudentName
GO-- Clean upDROP TABLE #TestTableGO