Join with comma separated values in SQL Server

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          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.

Answer 1

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, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').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;

Answer 2

Same solution as the one provided by Aaron Bertrand when it comes to building the comma separated values but a bit different in connecting CourseMaster.CourseId with the values in StudentMaster.Course.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

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,06'),                      
('25695', 'HARI',  'HARI ADDRESS',  '02,06'),                         
('89685', 'JEFF',  'JEFF ADDRESS',  '03,05,06,07'),                   
('47896', 'DAISY', 'DAISY ADDRESS', '03');

Query 1:

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;

Results:

| 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 |

Answer 3

A simple way to get the list values from the StudentMaster table (and you can join against the results) would be to (with the help of a split string as mentioned earlier replies, and assuming that function returns a column called item for each list item):

SELECT ROLLNO, NAME, ADDRESS, item
FROM StudentMaster
CROSS APPLY fSplitString(Course);

Answer 4

select D.DATE,D.DESCP,
   (
   select ','+S.NAME
   from tb_schems as S
   where ',' + D.SCHME + ',' like '%,' + cast(S.id as nvarchar(20)) + ',%'
   for xml path(''), type
   ).value('substring(text()[1], 2)', 'varchar(max)') as SCHEME
from TB_DETAIL as D;