Concatenation Using For XML Path with a reference to Junction Table in SQL Server

Introduction

Suppose we have the below two tables say Course,Students

--Declare the table variables
DECLARE @Course table(CourseId int Primary key identity(1,1),CourseName Varchar(50));
DECLARE @Students table(StudentId int Primary key identity(1,1),StudentName Varchar(50));

-- Insert Records
Insert into @Course values('C#'),('Asp.net'),('Sqlserver'),('MySql')
Insert into @Students values('John'),('David'),('Hendry'),('Smith'),('Watson')

From the above we can make out that, many courses can be applied to many students (M:M) e.g. ("C# course" may be attributed to "John" and "David". "Asp.net" may be applied for "Smith". "Sqlserver" may be attributed to "David" , "Hendry", "Watson" etc...).

In such a case since it goes for a M:M relationship, to solve this kind of problem, the better approach is to maintain a Junction table which allows to create the many-to-many relationship and help us to abstain from duplicate entries.

Quoting Wikipedia

In database management systems following the relational model, a junction table is a database table that contains common fields from two or more other database tables within the same database. It is the standard way of creating a many-to-many relationship between tables.

The below example will give a better picture about it. Let us create another table say CourseAlloted table which acts as a Junction table in this context

--Declare the table variables
DECLARE @CourseAlloted table(CourseStudentAllotId int Primary key identity(1,1),CourseId int,StudentId int);

-- Insert Records
Insert into @CourseAlloted values (1,1),(1,3),(2,1),(1,2),(3,4),(3,5)

We can figure out that in CourseAlloted table, primary keys of both Course(CourseId) and Students(StudentId) are present. This becomes the navigational key at the time of joining the two tables. Once we run the above junction table, we get the below output

Our final objective is to bring the below desire result

Using the code

To find out the what Student are enrolled into which course, we can use the junction table in the following fashion:

;WITH studentCourseCTE AS(
SELECT 
        
        c.CourseName,
        s.StudentName,
        ca.CourseId,
        ca.StudentId
FROM @Course c 
JOIN @CourseAlloted ca on c.CourseId  = ca.CourseId
JOIN @Students s on s.StudentId = ca.StudentId)

SELECT *
FROM studentCourseCTE

Upon executing, we will get the below result

So now we have learnt as how to use junction table to make a query of entities that are in M:M (Many To Many) relationships.

But we can figure out that, CourseName and StudentName are appearing in individual rows. We need to devise a mechanism by which we can concatenate the rows into columns.

For that to happen, we need to use For XML Path (though other options are also available, but that is not of discussion for this topic). The For XML Path returns the query results in the form of XML.

So let us go ahead and implement the next query

concateStudentCourseCTE AS(
SELECT 
        DISTINCT  
        c2.CourseName,  
        (	SELECT ','+ c1.StudentName 
				FROM studentCourseCTE c1 
				WHERE c1.CourseId=c2.CourseId 
				FOR XML PATH('') 
		) StudentName
FROM studentCourseCTE c2)

Observe the value passed inside the PATH mode. The ('') inside the For XML Path is used to remove wrapper node, that is being created automatically.To make it more clear, let's execute the below

SELECT 
        DISTINCT  
        c2.CourseName,  
        (	SELECT ','+ c1.StudentName 
				FROM studentCourseCTE c1 
				WHERE c1.CourseId=c2.CourseId 
				FOR XML PATH('Root') 
		) StudentName
FROM studentCourseCTE c2

And the result

The <Root> is created for every row and the For XML Path is performing the concatenation along with the join condition

WHERE c1.CourseId=c2.CourseId

Now, once done, the output will be as under

We can figure out that, the StudentName column values are prepended by a leading comma(,). To remove that, we need to take help of Stuff Function (though other options are also available like Left) as shown under

SELECT 
        DISTINCT  
        c2.CourseName,  
        STUFF((	SELECT ','+ c1.StudentName 
				FROM studentCourseCTE c1 
				WHERE c1.CourseId=c2.CourseId 
				FOR XML PATH('') 
		),1,1, '') StudentName
FROM studentCourseCTE c2

And for generating the SlNo column sequentially at run time, we can use Row_Number function. The entire query is presented below

--Declare the table variables
DECLARE @Course table(CourseId int Primary key identity(1,1),CourseName Varchar(50));
DECLARE @Students table(StudentId int Primary key identity(1,1),StudentName Varchar(50));
DECLARE @CourseAlloted table(CourseStudentAllotId int Primary key identity(1,1),CourseId int,StudentId int);

-- Insert Records
Insert into @Course values('C#'),('Asp.net'),('Sqlserver'),('MySql')
Insert into @Students values('John'),('David'),('Hendry'),('Smith'),('Watson')
Insert into @CourseAlloted values (1,1),(1,3),(2,1),(1,2),(3,4),(3,5)

--The query
;WITH studentCourseCTE AS(
SELECT 
        
        c.CourseName,
        s.StudentName,
        ca.CourseId,
        ca.StudentId
FROM @Course c 
JOIN @CourseAlloted ca on c.CourseId  = ca.CourseId
JOIN @Students s on s.StudentId = ca.StudentId)

,concateStudentCourseCTE AS(
SELECT 
        DISTINCT  
        c2.CourseName,  
        STUFF((	SELECT ','+ c1.StudentName 
				FROM studentCourseCTE c1 
				WHERE c1.CourseId=c2.CourseId 
				FOR XML PATH('') 
		),1,1, '') StudentName
FROM studentCourseCTE c2)

SELECT 
        Slno = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
        ,CourseName [Course Name]
        ,StudentName [Student Name]
FROM concateStudentCourseCTE

References

a) SQL Server - Retrieve data in XML format

b) Stuff

Conclusion

So in this article, we learnt

1. What is a Junction table
2. Purpose of Junction table
3. How to use Junction table for retrieving values in a M:M (Many To Many) relationship.
4. Concatenate Rows by using For XML Path
5. Use of Stuff function
6. Generate sequential numbers at run time using Row_Number() function.

Hope this will be helpful. Thanks for reading. Zipped file attached.