Row Concatenation using For XML Path

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 252
--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

/* Result
--------------
Slno Course Name Student Name
1 Asp.net John
2 C# John,Hendry,David
3 Sqlserver Smith,Watson

Comments or Responses

Login to post response