sql - 使用SQL查询显示多个值

我试图在一个列中显示每个标题的多个作者。目前，由于某些Titles具有不止1个FirstName，因此存在重复行。是否有一种可用于解决此问题的连接形式，并将所有作者显示在一个字段中，并可能用逗号分隔。

这是我当前的查询:

SELECT
    Submission.Title, Researcher.FirstName, Submission.Type
FROM
    Submission
INNER JOIN
    ((Faculty
INNER JOIN
    School ON Faculty.FacultyID = School.[FacultyID])
INNER JOIN
    (Researcher
INNER JOIN
    ResearcherSubmission ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID)
             ON School.SchoolID = Researcher.SchoolID)
             ON Submission.SubmissionID = ResearcherSubmission.SubmissionID
GROUP BY
    Submission.Title, Researcher.FirstName, Submission.Type;

它生成的输出:

[

这是我试图生成的输出:

Title                        FirstName                         Type
---------------------------------------------------------------------------
21st Century Business        Matthew, Teshar                   Book Chapter
A Family Tree...             Keshant, Lawrence                 Book Chapter
Benefits of BPM...           Jafta                             Journal Article
Business Innovation          Matthew, Morna, Teshar            Book Chapter

最佳答案

您可以将交叉逻辑包含在CROSS APPLY中

SELECT
      Submission.Title
    , CA.FirstNames
    , Submission.Type
FROM Submission
      CROSS APPLY (
            SELECT
                  STUFF((
                        SELECT /* DISTINCT ??? */
                              ', ' + r.FirstName
                        FROM ResearcherSubmission rs
                              INNER JOIN Researcher r ON r.ResearcherID = rs.ResearcherID
                        WHERE Submission.SubmissionID = rs.SubmissionID
                        FOR XML PATH (''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
          ) AS CA (FirstNames)
GROUP BY
      Submission.Title
    , CA.FirstNames
    , Submission.Type
;

注意:我不确定在连接名称时是否需要在子查询中包括DISTINCT，例如如果这些是'Jane'(Smith)和'Jane'(Jones)，您是否希望最终列表为:'Jane'或'Jane，Jane'？