Have you ever encountered a situation where you needed to group rows and columns in SQL Server to display a desired result? If so, you’re not alone. In this blog post, we will explore a simple yet efficient trick using XML PATH to achieve this.

Let’s consider a scenario where we have a table of students and the courses they are enrolled in, along with the name of the professor. We want to group the result by course and instructor name. Here is an example of the table:

To achieve the desired result, we can use the XML PATH function in SQL Server. This function allows us to combine two or more columns together and display the result in a desired format. Here is a script that demonstrates how to use XML PATH to group the data:

-- Create table
CREATE TABLE #TestTable (StudentName VARCHAR(100), Course VARCHAR(100), 
Instructor VARCHAR(100), RoomNo VARCHAR(100))

-- Populate table
INSERT INTO #TestTable (StudentName, Course, Instructor, RoomNo)
SELECT 'Mark', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Mark', 'Maths', 'Dr. Jones', '201'
UNION ALL
SELECT 'Joe', 'Algebra', 'Dr. James', '101'
UNION ALL
SELECT 'Joe', 'Science', 'Dr. Ross', '301'
UNION ALL
SELECT 'Joe', 'Geography', 'Dr. Lisa', '401'
UNION ALL
SELECT 'Jenny', 'Algebra', 'Dr. James', '101'

-- Group by Data using column and XML PATH
SELECT
StudentName,
STUFF((
SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + 
' in Room No ' + CAST(RoomNo AS VARCHAR(MAX))
FROM #TestTable
WHERE (StudentName = StudentCourses.StudentName)
FOR XML PATH (''))
,1,2,'') AS NameValues
FROM #TestTable StudentCourses
GROUP BY StudentName

-- Clean up
DROP TABLE #TestTable

By using the XML PATH function along with the STUFF function, we can concatenate the course, instructor, and room number values for each student into a single string. The result is grouped by the student’s name.

This simple trick allows us to efficiently group rows and columns in SQL Server, providing a clean and concise result. Feel free to try this script on your own machine and let me know if you have any questions or if you have found a better way to achieve the same result.

Thank you for reading!