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: 3692
  • 0 Comments

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

Print
Categories: Blogs
Tags:
Rate this article:
No rating

Please login or register to post comments.