Multiple rows into a single row and column

Today I wanted to quick mention a technique to bind a one to many in one column as a list of items. I know it has been done before a little differently in previous blogs before on BIDN.

The problem?

We have three tables

Instructors – InstructorClasses -   Classes

In the instructors table we have the instructors name, ID, and yada yada. Instructor classes is our junction table and links all of the instructors to their list of classes. Finally, classes is our simple list of classes.  Now we have a web page which we need a query to look like so.

Will here is a simple solution below.

SELECT  Left(Main.Categories,Len(Main.Categories)-1) as Classes, Instructors.FirstName + ' '  + Instructors.LastName as InstructorsName
FROM Instructors
	left join (Select distinct ST2.InstructorID , 
			   (Select distinct Classes.ClassTitle + '; ' AS [text()]
				From InstructorsClasses ST1
				join dbo.Classes   
				on    ST1.ClassID = Classes.ClassID
				Where ST1.InstructorID = ST2.InstructorID 
				For XML PATH ('')             
				) [Categories]
	            
	   From InstructorsClasses ST2) [Main]
	   on InstructorID = main.InstructorID 

SSIS Training - SSAS Training - SSRS Training