在 SQL Server 中加入逗号分隔值

你真的应该有一个学生正在学习的课程的连接表，而不是将逗号分隔的值塞进一个单独的元组中。如果您认为这是由于这种次优设计而导致的最后一个问题，那么您将大吃一惊。你真的应该让这个项目的所有者阅读规范化 - 是的，改变你的模式很痛苦，但不断处理保持原样的限制也是如此。

无论如何，话虽如此，您需要一个拆分功能。由于您的逗号分隔值是数字，因此您可以使用我的 XML 函数的变体；在这篇博文中还有其他几个可供选择。

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

现在，您的查询是：

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

同样，这是一个复杂的解决方案，并且由于您的数据库结构较差，您必须执行的下一个查询将同样复杂和繁琐。几乎所有关于该主题的博客、文章或书籍都反对这种类型的设计是有原因的……

在更现代的版本（SQL Server 2017 及更高版本）中，您可以组合STRING_AGG()并STRING_SPLIT()使其更简单：

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;

CourseMaster.CourseId与 Aaron Bertrand 在构建逗号分隔值时提供的解决方案相同，但在连接StudentMaster.Course.

SQL小提琴

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

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

结果：

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

从表中获取列表值的一种简单方法StudentMaster（您可以根据结果加入）是（借助前面提到的拆分字符串回复，并假设该函数为每个列表项返回一个名为 item 的列） ：

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;