Join with comma separated values in SQL Server

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          Course
------------------------------------------------
12345   RAM     RAM ADDRESS      01,02,06                      
25695   HARI    HARI ADDRESS     02,06                         
89685   JEFF    JEFF ADDRESS     03,05,06,07                   
47896   DAISY   DAISY ADDRESS    03

Here I want to fetch the Student details with CourseName(Not CourseId).

If the values in Course is not comma separated than it would be very simple query to fetch the details with join.

As of my knowledge I can run two queries for the same result what I want, One query for fetching the details of student from [StudentMaster] to the front end. And other one for only fetching the CourseName from [CourseMaster] by corresponding CourseId through a loop.

But the fact I want the result by only one query rather than write two queries for this small task.

I guess it is 100% possible. And my expected result will look like:

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

Thank you and any valuable suggestion will be highly appreciate.

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to change your schema, but so is constantly dealing with the limitations of leaving it like it is.

Anyway, with that said, you need a split function. Since your comma-separated values are numeric, you can get away with a variation on my XML function; there are several others to choose from in this blog post.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1) = ','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '' 
          + REPLACE(@List, @Delimiter, '') 
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Now, your query is:

;WITH x AS 
(
  SELECT s.ROLLNO, s.Name, s.Address, c.CourseId, c.CourseName
  FROM dbo.StudentMaster AS s
  CROSS APPLY dbo.SplitStrings_XML(s.Course, default) AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.item = c.CourseId
)
SELECT ROLLNO, Name, Address, STUFF((
  SELECT ',' + CourseName FROM x AS x2 
  WHERE x2.ROLLNO = x.ROLLNO
  ORDER BY CourseId FOR XML PATH, 
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM x
GROUP BY ROLLNO, Name, Address;

Again, this is a complicated solution, and because of your inferior database structure, the next query you have to perform will be equally convoluted and cumbersome. There's a reason this type of design is argued against in just about every blog, essay, or book about the topic...

In more modern versions (SQL Server 2017 and above), you can combine STRING_AGG() and STRING_SPLIT() to make this a little simpler:

SELECT s.ROLLNO, s.NAME, s.ADDRESS, STRING_AGG(c.CourseName, ',')
  FROM dbo.StudentMaster AS s
  CROSS APPLY STRING_SPLIT(s.Course, ',') AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.value = c.CourseId
  GROUP BY s.ROLLNO, s.NAME, s.ADDRESS;

StackExchange Database Administrators Q#112408, answer score: 10