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

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.

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

StackExchange Database Administrators Q#317397, answer score: 5