将SQL行与多个值串联[重复]

这里有一个基于您的结果的XML示例。现在,您可以将查询放入CTE中,并将此解决方案应用于结果。

declare @tbl as table (
    txtFullName varchar(15)
    ,firstActivity varchar(15)
)

insert into @tbl values ('Joe Bloggs', 'Football')
insert into @tbl values ('Joe Bloggs', 'Tennis')
insert into @tbl values ('Katie Bloggs', 'Tennis')



SELECT
    txtFullName
    ,STUFF(
        (SELECT ', ' + firstActivity
        FROM @tbl
        WHERE txtFullName = a.txtFullName
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS firstActivity
FROM @tbl a
GROUP BY txtFullName

更新:

SELECT
    t0.txtFullName AS 'Name'
    ,t1.[FirstActivity]
FROM (
    SELECT txtFullName FROM tblMembers WHERE txtForm = '10'
) T0 
LEFT JOIN (
    SELECT
        txtFullName
        ,STUFF(
        (SELECT ', ' + txtName
        FROM tblLists
        INNER JOIN tblAllLists
            ON tblLists.intID = tblAllLists.intID
        INNER JOIN tblMembers
            ON tblAllLists.instuiID = tblMembers.instuiID
        WHERE txtFullName = M.txtFullName
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS firstActivity
    FROM tblLists
    INNER JOIN tblAllLists
        ON tblLists.intID = tblAllLists.intID
    INNER JOIN tblMembers M
        ON tblAllLists.instuiID = M.instuiID 
    WHERE txtDay = 'Mon' AND txtForm = '10' AND txtDesc='Norm'
    GROUP BY txtFullName
) T1
    ON t0.txtFullName = t1.txtFullName

您可以使用 STRING_AGG (自SQL Server 2017年起)

SELECT
    txtFullName,
    STRING_AGG(txtName,',')  AS 'First Activity'
FROM
    tblLists
    INNER JOIN tblMembers ON
        tblLists.txtInstituID = tblMembers.txtInstituID
    INNER JOIN tblAllLists ON
        tblLists.intID = tblAllLists.intID 
WHERE
  Day = 'Mon' AND
  txtForm = '10' AND
  Desc='Norm' AND
  txtID = '10'
GROUP BY
    txtFullName