使用SQL查询显示多个值

Question

我试图在单个列中显示每个标题的多个作者。此刻有一个重复的行，由于一些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 

Answer 1

您可以inclde一个CROSS内concantenation逻辑应用于

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到子查询如果这些是'简'（史密斯）和'简'（琼斯），你最终的名单是：'简'还是'简，简'？

Answer 2

您也可以在应用程序逻辑中执行此操作。

但是，如果你想用查询来做到这一点。你应该能够做这样的事：

SELECT DISTINCT 
    sm.Title, 
    STUFF(
     (SELECT  ', ' + r.FirstName 
     FROM ResearcherSubmission rs 
      INNER JOIN Researcher r ON r.ResearcherID = rs.ResearcherID 
     WHERE  sm.SubmissionID = rs.SubmissionID 
     FOR XML PATH('')), 1, 2, '') AS FirstNames, 
     sm.Type 
FROM Submission sm 

Answer 3

您可以使用下面的查询，你从O/P，你已经得到想要生成的O/P。

CREATE TABLE #temptable(Title VARCHAR(200), FirstName VARCHAR(200), Type VARCHAR(200)) 
    INSERT INTO #temptable 
    SELECT 'Book1','Matt','Chapter' UNION 
    SELECT 'Book1','Tesh','Chapter' UNION 
    SELECT 'BPM','Jafta','Article' UNION 
    SELECT 'Ethics','William','Journal' UNION 
    SELECT 'Ethics','Lawrence','Journal' UNION 
    SELECT 'Ethics','Vincent','Journal' UNION 
    SELECT 'Cellular','Jane','Conference' 
    SELECT Title 
      ,STUFF((SELECT ', ' + CAST(FirstName AS VARCHAR(10)) [text()] 
      FROM #temptable 
      WHERE Title = t.Title 
      FOR XML PATH(''), TYPE) 
      .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output 
      ,Type 
    FROM #temptable t 
    GROUP BY Title,Type