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.
Join the conversation! Your thoughts help the community grow.