View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
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.
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
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
SSIS Training - SSAS Training - SSRS Training