sql-server - 连接表内部查询中的逗号分隔字符串 (SQL Server)

Question

我搜索了很多线程、站点和博客以及 API，但似乎无法弄清楚这一点。我想从联结表中的行创建一个 CSV 字符串，但这里有问题。它需要用于整个数据集。我知道，令人困惑，这是分割。

让我们使用下表的通用示例:

• 学生(身份证、姓名、Gpa)
• 类(class)(ID、部门、级别)
• StudentsInClasses (StudentID, ClassID)

假设我想查询所有类(class)，但在该查询中(针对每条记录)我还想获得每个类(class)学生 ID 的 CSV 字符串。结果应如下所示:

Class      StudentsID_CSVString
-----      --------------------
BA302      1,2,3,4,5,6,7,8,9
BA479      4,7,9,12,15
BA483      7,9,12,18

我尝试使用如下所示的 coalesce 语句，但由于 coalesce 的愚蠢语法，我无法让它工作。有没有更好的方法来实现这个目标，还是我只是犯了一个愚蠢的语法错误？

declare @StudentsID_CSVString varchar(128)
select Dept + Level as 'Class',
    coalesce(@StudentsID_CSVString + ',', '')
        + CAST(StudentID as varchar(8)) as 'StudentsID_CSVString'
from Classes
    left outer join StudentsInClasses ON Classes.ID = StudentsInClasses.ClassID

我用下面的代码来测试:

declare @Students table (ID int, Name varchar(50), Gpa decimal(3,2))
declare @Classes table (ID int, Dept varchar(4), [Level] varchar(3))
declare @StudentsInClasses table (StudentID int, ClassID int)
declare @StudentsID_CSVString varchar(128)

insert into @Students (ID) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18)
insert into @Classes (ID, Dept, [Level]) values (1, 'BA', '302'), (2, 'BA', '379'), (3, 'BA', '483')
insert into @StudentsInClasses (StudentID, ClassID) values
    (1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),
    (4,2),(7,2),(9,2),(12,2),(15,2),
    (7,3),(9,3),(12,3),(18,3)

Answer 1

使用用逗号分隔的字符串填充变量 @StudentsID_CSVString 的技术，您一次只能获取一个类(class)的字符串。要在结果集中获得多个类，您可以使用 for xml path。

select C.Dept+C.Level as Class,
       stuff((select ','+cast(S.StudentID as varchar(10)) 
              from StudentsInClasses as S
              where S.ClassID = C.ID
              for xml path('')), 1, 1, '') as StudentsID_CSVString
from Classes as C

在此处测试查询:https://data.stackexchange.com/stackoverflow/q/115573/