Business Intelligence Blogs

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.

Multiple rows into a single row and column

  • 9 January 2011
  • Author: Ben Evans
  • Number of views: 3728

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.

Jim Bean | Rowing; Yoga; Bartending 


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

Categories: Blogs
Rate this article:
No rating

Please login or register to post comments.