Sql Server String column concat and use of group by clause

Question

I have a table like this:

Name          Address                             Matches
Tim Jones     1 London Road, London, W10EU        Peter Jones
Tim Jones     1 London Road, London, W10EU        Smith Jones
Tim Jones     1 London Road, London, W10EU        Tim Jones
Tim Jones     1 London Road, London, W10EU        S Singh
Jack Sons     10 West Street, London, W900U       John Graham
Jack Sons     10 West Street, London, W900U       Jack Sons

I want the result like this:

Name          Address                          Matches
Tim Jones     1 London Road, London, W10EU     Peter Jones,Smith johns,Tim Johns, S Singh
Jack Sons     10 West Street, London, W900U    John Graham, Jack Sons

It should concatenate all 'Matches' column and group by name, address. SQLServer08

score 0 · Answer 1 · answered Aug 06 '13 at 13:46

Select distinct b.name, b.address,
               substring((Select ','+a.matches  AS [text()]
                From test15 a
                Where a.name=b.name and a.Address=b.Address
                ORDER BY a.name, a.address
                For XML PATH ('')),2, 1000) [Match]
         From dbo.test15 b

Sql Server String column concat and use of group by clause

1 Answers1