sql-server - 使用 STUFF 或 CONCAT 命令在 T-SQL 中组合行

Question

我正在考虑修改程序中的以下查询(这是在我担任软件工程师之前写的，所以请多多包涵...):

SELECT
        Participant.ParticipantID            AS "ParticipantId",
        Stream.StreamName                    AS "StreamName",
        ParticipantStatistics.ConnectTime    AS "ConnectTime",
        ParticipantStatistics.DisconnectTime AS "DisconnectTime",
        FormField.FieldLabel                 AS "FieldLabel",
        RegistrantAnswer.Answer              AS "Answer"
    FROM   ParticipantStatistics
        INNER JOIN Participant ON ParticipantStatistics.ParticipantId = Participant.ParticipantID
        INNER JOIN Registrant ON Registrant.RegistrantId = Participant.RegistrantID
        LEFT OUTER JOIN RegistrantAnswer ON RegistrantAnswer.RegistrantID = Registrant.RegistrantID
        INNER JOIN Event ON Event.EventId = Participant.EventID
        INNER JOIN Stream ON Stream.MediaEventId = Event.EventId
        LEFT OUTER JOIN FormField ON RegistrantAnswer.FormFieldId = FormField.FormFieldId
        LEFT OUTER JOIN (SELECT DISTINCT ParticipantID, SurveyID FROM ParticipantSurvey) 
        AS SurveyCompleted ON SurveyCompleted.ParticipantID = Participant.ParticipantID
    WHERE  Stream.StreamId = '2235'
        AND Participant.Visible = 1
        ORDER  BY Participant.ParticipantID, OrderNumber, ParticipantStatistics.ConnectTime ASC

此查询为我提供了以下信息:

我想做的是修改上面的查询，将结果作为一行返回，如下所示:

| 315314 | ffbc110729 | 2011-10-27 03:13:06.240 | 2011-10-27 03:17:12.473 | **First Name, Last Name, Email, Company** | **ads, asd, asd@asd.com, asdf** |

最后两列组合在一起，在一行中用逗号分隔。

这可以使用 STUFF 或 CONCAT 吗？我是 T-SQL 的新手，所以如果您需要进一步说明，请告诉我。

最好的问候，

编辑:当我尝试使用 STUFF FOR XML PATH 进行编辑时，我按以下方式进行设置:

    SELECT 
        Participant.ParticipantID            AS "ParticipantId",
        Stream.StreamName                    AS "StreamName",
        ParticipantStatistics.ConnectTime    AS "ConnectTime",
        ParticipantStatistics.DisconnectTime AS "DisconnectTime",
    STUFF ((SELECT ','+ FormField.FieldLabel FROM FormField WHERE FormField.FormFieldID = RegistrantAnswer.FormFieldID FOR XML PATH ('')),1,1,'')
    FROM   ParticipantStatistics
        INNER JOIN Participant ON ParticipantStatistics.ParticipantId = Participant.ParticipantID
        INNER JOIN Registrant ON Registrant.RegistrantId = Participant.RegistrantID
        LEFT OUTER JOIN RegistrantAnswer ON RegistrantAnswer.RegistrantID = Registrant.RegistrantID
        INNER JOIN Event ON Event.EventId = Participant.EventID
        INNER JOIN Stream ON Stream.MediaEventId = Event.EventId
        LEFT OUTER JOIN FormField ON RegistrantAnswer.FormFieldId = FormField.FormFieldId
        LEFT OUTER JOIN (SELECT DISTINCT ParticipantID, SurveyID FROM ParticipantSurvey) 
        AS SurveyCompleted ON SurveyCompleted.ParticipantID = Participant.ParticipantID
    WHERE  Stream.StreamId = '2235'
        AND Participant.Visible = 1
        ORDER  BY Participant.ParticipantID, OrderNumber, ParticipantStatistics.ConnectTime ASC

然后我收到以下内容:

同样，我对 T-SQL 还很陌生，所以也许我设置有误？非常感谢任何帮助。

Answer 1

尝试这样的事情:

SELECT
    Participant.ParticipantID            AS "ParticipantId",
    Stream.StreamName                    AS "StreamName",
    ParticipantStatistics.ConnectTime    AS "ConnectTime",
    ParticipantStatistics.DisconnectTime AS "DisconnectTime",
    STUFF(
    (SELECT ', ' + FormField.FieldLabel as'text()'
       FROM Registrant
          LEFT OUTER JOIN RegistrantAnswer ON RegistrantAnswer.RegistrantID = Registrant.RegistrantID
          LEFT OUTER JOIN FormField ON RegistrantAnswer.FormFieldId = FormField.FormFieldId
       WHERE Registrant.RegistrantId = Participant.RegistrantID
       FOR XML PATH('')
     ), 1, 2, '') AS "FieldLabel",
    STUFF(
    (SELECT ', ' + RegistrantAnswer.Answer as'text()'
       FROM Registrant
          LEFT OUTER JOIN RegistrantAnswer ON RegistrantAnswer.RegistrantID = Registrant.RegistrantID 
       WHERE Registrant.RegistrantId = Participant.RegistrantID
       FOR XML PATH('')
     ), 1, 2, '') AS "Answer"
FROM   ParticipantStatistics
    INNER JOIN Participant ON ParticipantStatistics.ParticipantId = Participant.ParticipantID
    INNER JOIN Registrant ON Registrant.RegistrantId = Participant.RegistrantID
    INNER JOIN Event ON Event.EventId = Participant.EventID
    INNER JOIN Stream ON Stream.MediaEventId = Event.EventId
WHERE  Stream.StreamId = '2235'
    AND Participant.Visible = 1
ORDER  BY Participant.ParticipantID, OrderNumber, ParticipantStatistics.ConnectTime ASC