[This is an old but regularly requested post of mine from BIDN in 2011, updated due to my renowned grammar]
Today I wanted to quickly mention a technique to pivot a one to many data structure into a one column list of items. I know it has been mentioned before a bit differently in previous blogs before on BIDN.
We have three tables
Instructors – InstructorClasses – Classes
In the instructors table we have the instructors name, ID, and yada yada. InstructorClasses is our junction table and links all of the instructors to their list of classes and finally classes is our simple list of classes. Now assuming we have a web page that needs to show them as below?
Instructor: Jim Bean
Classes: Rowing; Yoga; Bartending;
Now assuming you are unable to format in code or perhaps you are archiving data and needing to convert it during ETL. To get the expected results we can utilize the XML Path command in SQL Server to provide us a pivoted view of classes as demonstrated in the example below.
SELECT Left(Main.Categories,Len(Main.Categories)-1) as Classes, Instructors.FirstName + ' ' + Instructors.LastName as InstructorsName
left join (Select distinct ST2.InstructorID ,
(Select distinct Classes.ClassTitle + '; ' AS [text()]
From InstructorsClasses ST1
on ST1.ClassID = Classes.ClassID
Where ST1.InstructorID = ST2.InstructorID
For XML PATH ('')
From InstructorsClasses ST2) [Main]
on InstructorID = main.InstructorID