Group by Rows and Columns using XML PATH – Efficient CONCAT Trick

vedi anche qui.

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?

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:

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

-- 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'
GO

-- Check orginal data
SELECT *
FROM #TestTable
GO

-- 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
GO

-- Clean up
DROP TABLE #TestTable
GO

Trovato qui.