How to Get Comma Separated Values in SQL Server With Group By Facility

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.

1. Student
   
   1. CREATE TABLE [dbo].[Student](
   3. [StudentId] [int] IDENTITY(1,1) NOT NULL,
   5. [StudentName] [varchar](50) NOT NULL,
   7. [FatherName] [varchar](50) NOT NULL,
   9. [Address] [varchar](100) NOT NULL,
   11. [MobileNo] [varchar](11) NOT NULL,
   13. CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
   15. (
   17. [StudentId] ASC
   19. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   21. ) ON [PRIMARY]
2. Course
   
   1. CREATE TABLE [dbo].[Course](
   3. [CourseId] [int] IDENTITY(1,1) NOT NULL,
   5. [courseName] [varchar](50) NOT NULL,
   7. CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
   9. (
   11. [CourseId] ASC
   13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   15. ) ON [PRIMARY]
3. Transecting table
   
   The following is the Transecting table that contains the data about the student course list (trnjCourse_Studnet).
   
   1. CREATE TABLE [dbo].[trnjCourse_Studnet](
   3. [Id] [int] IDENTITY(1,1) NOT NULL,
   5. [StudentId] [int] NOT NULL,
   7. [CourseId] [int] NOT NULL,
   9. CONSTRAINT [PK_trnjCourse_Studnet] PRIMARY KEY CLUSTERED
   11. (
   13. [Id] ASC
   15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   17. ) ON [PRIMARY]
   19. GO
   21. ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_CourseId] FOREIGN KEY([CourseId])
   23. REFERENCES [dbo].[Course] ([CourseId])
   25. GO
   27. ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_CourseId]
   29. GO
   31. ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_StudentId] FOREIGN KEY([StudentId])
   33. REFERENCES [dbo].[Student] ([StudentId])
   35. GO
   37. ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_StudentId]
   39. GO