SQL Voodoo Query: Merging a column in multiple rows into just one

[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.

The problem?

We have three tables

InstructorsInstructorClassesClasses

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

Leave a Reply

Your email address will not be published. Required fields are marked *