Junte-se com valores separados por vírgula no SQL Server

Você realmente deveria ter uma tabela de junção para os cursos que um aluno está fazendo, em vez de agrupar valores separados por vírgulas em uma única tupla. Se você acha que esse é o último problema que terá por causa desse design abaixo do ideal, terá uma grande surpresa. Você realmente deveria fazer com que os proprietários deste projeto lessem sobre normalização - sim, é doloroso alterar seu esquema, mas também está constantemente lidando com as limitações de deixá-lo como está.

De qualquer forma, com isso dito, você precisa de uma função de divisão. Como seus valores separados por vírgula são numéricos, você pode se safar com uma variação da minha função XML; há vários outros para escolher nesta postagem do blog .

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

Agora, sua consulta é:

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

Novamente, esta é uma solução complicada e, devido à sua estrutura de banco de dados inferior, a próxima consulta que você precisa executar será igualmente complicada e complicada. Há uma razão pela qual esse tipo de design é contestado em quase todos os blogs, ensaios ou livros sobre o assunto...

Em versões mais modernas (SQL Server 2017 e superior), você pode combinar STRING_AGG()e STRING_SPLIT()tornar isso um pouco mais simples:

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;

Mesma solução que a fornecida por Aaron Bertrand quando se trata de construir os valores separados por vírgula, mas um pouco diferente na conexão CourseMaster.CourseIdcom os valores em StudentMaster.Course.

SQL Fiddle

Configuração do esquema do MS SQL Server 2014 :

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');

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

Resultados :

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

Uma maneira simples de obter os valores da lista da StudentMastertabela (e você pode juntar os resultados) seria (com a ajuda de uma string dividida como mencionado nas respostas anteriores, e assumindo que a função retorna uma coluna chamada item para cada item da lista) :

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

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;