display more than one value using a SQL query

Question

I am trying to display multiple authors per title in a single column. At the moment there a repeating rows, due to the fact that some Titles have more than 1 FirstName. Is there a form of concatenation that can be used to resolve this and display all the authors in a single filed and perhaps separated by a comma.

This is my current query:

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;

This the output it generates:

[

this is the output I am trying to generate:

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

You can do this in your application logic as well.

But if you want to do this with a query. You should be able do something like this:

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 2

You may inclde the concantenation logic within a 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
;

NB: I'm not sure if you need to include DISTINCT into the subquery when concatenating the names, e.g. if these was 'Jane' (Smith) and 'Jane' (Jones) do you want the final list as: 'Jane' or 'Jane, Jane'?

Answer 3

You can use the below query to generate the o/p as you want from the o/p that you have got.

    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