Overview
Sometimes when tables have a one-to-many relationship or many-to-many relationship we need to create a report of comma separated strings along with the necessary primary information.
Here I will explain a scenario in which a student can join multiple courses. To do this I am creating three tables, two master and one transaction, tables named Student, Course and trnjCourse_Studnet. The Student table has student information. The Course table has course name and course ID. The transaction table contains the information about a student and courses is a one-to-many relationship.
Now I need to write a query for student joining multiple courses, then the entire course name that is joined by that student should be displayed, separated by a comma with the unique student information. Let's see how to do it.
The Scripts
The following are the scripts to create the table.
- 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]
- 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]
- Transecting table
The following is the Transecting table that contains the data about the 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 intention is to generate an output that contains student info along with the course name. If the student joins more than one course then the course name should display separated by commas as displayed in the following image.
For getting this output I am writing a 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 get comma separate a string in a SQL Server table with a 1 to many relationship with a table.
Thanks.
I would like to have feedback from my readers. Please post your feedback, question, or comments about this article.