How to get Comma Separated Values in SQL Server with 'Group By’ Facility?

Overview:

Sometimes when tables having one to many relationship or many to many relationship we need to create report in which comma separated string along with primary information is needed.

Here I am going to explain a scenario in which a student can join multiple courses to achieve this I am creating three tables two master and one transection tables named as Student, Course and trnjCourse_Studnet. Student table having student information. Course table having course name and course ID. The transection table contains the information about student and courses one to many relationship.

Now I have to write a query in which if student join multiple courses then then the entire courses name join by that student should display separated with comma with unique student information. Let’s see how to achieve it.

Following are the scripts to create table

1. Student

CREATE TABLE [dbo].[Student](

[StudentId] [int] IDENTITY(1,1) NOT NULL,

[StudentName] [varchar](50) NOT NULL,

[FatherName] [varchar](50) NOT NULL,

[Address] [varchar](100) NOT NULL,

[MobileNo] [varchar](11) NOT NULL,

CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED

(

[StudentId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. Course

CREATE TABLE [dbo].[Course](

[CourseId] [int] IDENTITY(1,1) NOT NULL,

[courseName] [varchar](50) NOT NULL,

CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED

(

[CourseId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

3. Tranjection table that contains data about student course list.( trnjCourse_Studnet)

CREATE TABLE [dbo].[trnjCourse_Studnet](

[Id] [int] IDENTITY(1,1) NOT NULL,

[StudentId] [int] NOT NULL,

[CourseId] [int] NOT NULL,

CONSTRAINT [PK_trnjCourse_Studnet] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_CourseId] FOREIGN KEY([CourseId])

REFERENCES [dbo].[Course] ([CourseId])

GO

ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_CourseId]

GO

ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_StudentId] FOREIGN KEY([StudentId])

REFERENCES [dbo].[Student] ([StudentId])

GO

ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_StudentId]

GO

Insert some data

 

After inserting the data our main intension is to generate an output that contains student info along with the course name. If student join more than one course the course name should display separated by comma displayed in following image.

 

For getting this output I am writing query using CTE.

WITH Courseslist (StudentId,StudentName,FatherName,MobileNo,CourseName ) as

(

select trnjCourse_Studnet.StudentId,StudentName ,FatherName,MobileNo,Coursename from dbo.trnjCourse_Studnet

left outer join Course

on Course.Courseid =trnjCourse_Studnet.Courseid

left outer join

Student

on trnjCourse_Studnet.StudentId =Student .StudentId

),

CourseListForStudent as(

SELECT StudentId ,StudentName,FatherName,MobileNo, LEFT(courseList , LEN(courseList)-1) AS CourseNameList

FROM Courseslist AS extern

CROSS APPLY

(

SELECT CourseName + ' , '

FROM Courseslist AS intern

WHERE extern.StudentId = intern.StudentId

FOR XML PATH('')

)pre_trimmed (courseList)

Group by StudentId,courseList,StudentName,FatherName,MobileNo

)

SELECT * FROM CourseListForStudent

Summary

This article showed how to comma separated string in SQL server table have 1 to many relationship with table.

Thanks.

I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article. 

Ebook Download
View all
Learn
View all