Join with comma separated values in SQL without direct relation between tables

Question

I have a table [CourseMaster] LIKE

CourseId    CourseName
-----------------------
  01          ABC
  02          DEF
  03          GHI
  04          JKL
  05          MNO
  06          PQR
  07          STU

And I have another table [StudentMaster] for Student Details LIKE

ROLLNO  NAME    ADDRESS       
------------------------------
12345   RAM     RAM ADDRESS                     
25695   HARI    HARI ADDRESS                   
89685   JEFF    JEFF ADDRESS              
47896   DAISY   DAISY ADDRESS 

And I have another table [StudentCourseMaster] for Student Course Details LIKE

ROLLNO     CourseId      
-------------------
12345      01             
12345      02                 
12345      06            
25695      02
25695      06
89685      03
89685      05
89685      06
89685      07
47896      03

I am trying to fetch the result in below format, but I am not able make the join.

ROLLNO  NAME    ADDRESS         Course
-------------------------------------------
12345   RAM     RAM ADDRESS     ABC,DEF,PQR                   
25695   HARI    HARI ADDRESS    DEF,PQR                       
89685   JEFF    JEFF ADDRESS    GHI,MNO,PQR,STU               
47896   DAISY   DAISY ADDRESS   GHI    

I have seen this example, but still not able to figure out.

Thank you and any valuable suggestion will be highly appreciate.

Answer 1

With SQL Server 2017 and later (including Azure SQL Database), use a correlated subquery with STRING_AGG to get the comma separated course list for each student:

SELECT
     sm.ROLLNO
    ,sm.NAME
    ,sm.ADDRESS
    ,(SELECT STRING_AGG(cm.CourseName,',')
     FROM dbo.StudentCourseMaster AS scm
     JOIN dbo.CourseMaster AS cm ON cm.CourseId = scm.CourseId
     WHERE scm.ROLLNO = sm.ROLLNO
     ) AS Courses
FROM dbo.StudentMaster AS sm;

In pre SQL 2017 versions, you'll need to use the XML PATH method in the subquery like in the example you posted instead of STRING_AGG.

Below is the DDL and sample data with constraints:

CREATE TABLE dbo.CourseMaster(
     CourseId int NOT NULL CONSTRAINT PK_CourseMaster PRIMARY KEY
    ,CourseName varchar(30) NOT NULL
);
INSERT INTO dbo.CourseMaster(CourseId, CourseName)
VALUES
     (01, 'ABC')
    ,(02, 'DEF')
    ,(03, 'GHI')
    ,(04, 'JKL')
    ,(05, 'MNO')
    ,(06, 'PQR')
    ,(07, 'STU');

CREATE TABLE dbo.StudentMaster(
     ROLLNO int NOT NULL CONSTRAINT PK_StudentMaster PRIMARY KEY
    ,NAME varchar(30) NOT NULL
    ,ADDRESS varchar(30) NOT NULL
);
INSERT INTO dbo.StudentMaster(ROLLNO, NAME, ADDRESS)
VALUES(
     12345, 'RAM', 'RAM ADDRESS')
    ,(25695, 'HARI', 'HARI ADDRESS')
    ,(89685, 'JEFF', 'JEFF ADDRESS')
    ,(47896, 'DAISY', 'DAISY ADDRESS');

CREATE TABLE dbo.StudentCourseMaster(
     ROLLNO int NOT NULL
    ,CourseId int NOT NULL
    ,CONSTRAINT PK_StudentCourseMaster PRIMARY KEY(ROLLNO,CourseId)
    ,CONSTRAINT FK_StudentCourseMaster_CourseMaster FOREIGN KEY(CourseId) REFERENCES dbo.CourseMaster(CourseId)
    ,CONSTRAINT FK_StudentCourseMaster_StudentMaster FOREIGN KEY(ROLLNO) REFERENCES dbo.StudentMaster(ROLLNO)
);
INSERT INTO dbo.StudentCourseMaster(ROLLNO, CourseId)
VALUES
     (12345, 01)             
    ,(12345, 02)                 
    ,(12345, 06)            
    ,(25695, 02)
    ,(25695, 06)
    ,(89685, 03)
    ,(89685, 05)
    ,(89685, 06)
    ,(89685, 07)
    ,(47896, 03);
GO

Answer 2

Try this:

;WITH StudentCourses AS (
SELECT sm.ROLLNO, STRING_AGG(cm.CourseName, ',') CourseNames FROM dbo.StudentMaster AS sm
LEFT JOIN dbo.StudentCourseMaster AS scm ON scm.ROLLNO = sm.ROLLNO
LEFT JOIN dbo.CourseMaster AS cm ON cm.CourseId = scm.CourseId
GROUP BY sm.ROLLNO
)
SELECT sm.*, sc.CourseNames FROM StudentCourses sc
    INNER JOIN dbo.StudentMaster AS sm ON sm.ROLLNO = sc.ROLLNO