SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table dbo.CourseMaster
(
  CourseId char(2),
  CourseName char(3)
);

create table dbo.StudentMaster
(
  ROLLNO char(5),
  NAME varchar(10),
  ADDRESS varchar(20),
  Course varchar(100)
);

insert into dbo.CourseMaster values
('01', 'ABC'),
('02', 'DEF'),
('03', 'GHI'),
('04', 'JKL'),
('05', 'MNO'),
('06', 'PQR'),
('07', 'STU');

insert into dbo.StudentMaster values
('12345', 'RAM',   'RAM ADDRESS',   '01,02,02'),                      
('25695', 'HARI',  'HARI ADDRESS',  '02,06'),                         
('89685', 'JEFF',  'JEFF ADDRESS',  '03,05,06,07'),                   
('47896', 'DAISY', 'DAISY ADDRESS', '03');
11 rows affected

select SM.ROLLNO,
       SM.NAME, 
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from dbo.CourseMaster as CM
       where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;


ROLLNO
NAME
ADDRESS
Course


12345
RAM
RAM ADDRESS
ABC,DEF

25695
HARI
HARI ADDRESS
DEF,PQR

89685
JEFF
JEFF ADDRESS
GHI,MNO,PQR,STU

47896
DAISY
DAISY ADDRESS
GHI


CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END
with normal_course as (
    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss
)
select SM.ROLLNO,
       SM.NAME, 
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from normal_course as nc 
       join dbo.CourseMaster as CM on CM.CourseId = nc.course_id
       where nc.ROLLNO = SM.ROLLNO
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;


ROLLNO
NAME
ADDRESS
Course


12345
RAM
RAM ADDRESS
ABC,DEF,DEF

25695
HARI
HARI ADDRESS
DEF,PQR

89685
JEFF
JEFF ADDRESS
GHI,MNO,PQR,STU

47896
DAISY
DAISY ADDRESS
GHI

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

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