Concatenating SQL rows with multiple values

Question

Current Table:

txtFullName  | First Activity
_____________________________

Joe Bloggs   | Football

Joe Bloggs   | Tennis    

Katie Bloggs | Tennis

Where I'm trying to get it as...

txtFullName  | First Activity
_____________________________
Joe Bloggs   | Football, Tennis

Katie Bloggs | Tennis

**I've tried using COALESCE AND FOR XML PATH('').. Which I can't seem to fit correctly in this statement.. is there another way to do the same thing?

Any help would be appreciated!!

What version of SQL-Server? For XML Path on most and ListAgg on newer versions — xQbert, Oct 31 '17 at 15:01

Valerica · Accepted Answer · 2017-11-01T14:20:24.700

2

Here have an example with XML based on your result. Now you can put your query inside of a CTE and apply this solution to the result.

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

UPDATE:

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

edited Nov 01 '17 at 14:20

answered Oct 31 '17 at 14:59

Valerica

1,618
1
13
20

It can be done in one query too, but i need to know the structure of tables. You can concatenate the activities inside of `LEFT JOIN` – Valerica Oct 31 '17 at 16:05
@dan6657 please see my updated answer. Hope it's working since it's untested – Valerica Nov 01 '17 at 09:41
Ah thankyou!!! This doesn't pull through the activities atall - however I can have a little fiddle with this now! – dan6657 Nov 01 '17 at 11:30
https://pastebin.com/Vqz48kmx - this one works as it should! – dan6657 Nov 01 '17 at 12:27
Here you go, it's even more ugly 'cause the query you provided me through the link was a little bit different than the original one (from the post) – Valerica Nov 01 '17 at 14:21
You're a saviour!! – dan6657 Nov 01 '17 at 14:59

Indent · Answer 2 · 2017-10-31T15:07:20.767

1

You can use STRING_AGG (since 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

edited Oct 31 '17 at 15:07

answered Oct 31 '17 at 14:57

Indent

4,675
1
19
35

Doesn't quite work on 14 which i'm currently working with, thanks though need to look at upgrading. – dan6657 Oct 31 '17 at 15:49

Concatenating SQL rows with multiple values

2 Answers2