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

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.

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.