Post
Ask Question

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](  
    2.   
    3. [StudentId] [int] IDENTITY(1,1) NOT NULL,  
    4.   
    5. [StudentName] [varchar](50) NOT NULL,  
    6.   
    7. [FatherName] [varchar](50) NOT NULL,  
    8.   
    9. [Address] [varchar](100) NOT NULL,  
    10.   
    11. [MobileNo] [varchar](11) NOT NULL,  
    12.   
    13. CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED  
    14.   
    15. (  
    16.   
    17. [StudentId] ASC  
    18.   
    19. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    20.   
    21. ) ON [PRIMARY]  
  2. Course
    1. CREATE TABLE [dbo].[Course](  
    2.   
    3. [CourseId] [int] IDENTITY(1,1) NOT NULL,  
    4.   
    5. [courseName] [varchar](50) NOT NULL,  
    6.   
    7. CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED  
    8.   
    9. (  
    10.   
    11. [CourseId] ASC  
    12.   
    13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    14.   
    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](  
    2.   
    3. [Id] [int] IDENTITY(1,1) NOT NULL,  
    4.   
    5. [StudentId] [int] NOT NULL,  
    6.   
    7. [CourseId] [int] NOT NULL,  
    8.   
    9. CONSTRAINT [PK_trnjCourse_Studnet] PRIMARY KEY CLUSTERED  
    10.   
    11. (  
    12.   
    13. [Id] ASC  
    14.   
    15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    16.   
    17. ) ON [PRIMARY]  
    18.   
    19. GO  
    20.   
    21. ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_CourseId] FOREIGN KEY([CourseId])  
    22.   
    23. REFERENCES [dbo].[Course] ([CourseId])  
    24.   
    25. GO  
    26.   
    27. ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_CourseId]  
    28.   
    29. GO  
    30.   
    31. ALTER TABLE [dbo].[trnjCourse_Studnet] WITH CHECK ADD CONSTRAINT [FK_StudentId] FOREIGN KEY([StudentId])  
    32.   
    33. REFERENCES [dbo].[Student] ([StudentId])  
    34.   
    35. GO  
    36.   
    37. ALTER TABLE [dbo].[trnjCourse_Studnet] CHECK CONSTRAINT [FK_StudentId]  
    38.   
    39. GO  

Insert some data

Insert data in sql

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.

student info

For getting this output I am writing a query using CTE.

  1. WITH Courseslist (StudentId,StudentName,FatherName,MobileNo,CourseName ) as  
  2.   
  3. (  
  4.   
  5. select trnjCourse_Studnet.StudentId,StudentName ,FatherName,MobileNo,Coursename from dbo.trnjCourse_Studnet  
  6.   
  7. left outer join Course  
  8.   
  9. on Course.Courseid =trnjCourse_Studnet.Courseid  
  10.   
  11. left outer join  
  12.   
  13. Student  
  14.   
  15. on trnjCourse_Studnet.StudentId =Student .StudentId  
  16.   
  17. ),  
  18.   
  19. CourseListForStudent as(  
  20.   
  21. SELECT StudentId ,StudentName,FatherName,MobileNo, LEFT(courseList , LEN(courseList)-1) AS CourseNameList  
  22.   
  23. FROM Courseslist AS extern  
  24.   
  25. CROSS APPLY  
  26.   
  27. (  
  28.   
  29. SELECT CourseName + ' , '  
  30.   
  31. FROM Courseslist AS intern  
  32.   
  33. WHERE extern.StudentId = intern.StudentId  
  34.   
  35. FOR XML PATH('')  
  36.   
  37. )pre_trimmed (courseList)  
  38.   
  39. Group by StudentId,courseList,StudentName,FatherName,MobileNo  
  40.   
  41. )  
  42.   
  43.   
  44. 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.

 


Similar Articles