SQL subquery return multiple values

Question

Please look at my query

select tms.TeamName,
(select loc.LocID from Locations loc, Users usr where loc.UserID = usr.UserID and usr.TeamID = tms.TeamID)
from Teams tms

In this case LocID returns multiple values. I want it to come in single column with comma separated values. How can i proceed?

Thanks in advance - Manoj

possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Martin Smith, Mar 14 '11 at 11:11

score 2 · Answer 1 · answered Mar 14 '11 at 13:14

2

select
  tms.TeamName,
  stuff(
    (select ','+cast(loc.LocID as varchar(10))
     from Locations as loc
      inner join Users as usr
        on loc.UserID = usr.UserID
     where usr.TeamID = tms.TeamID
     for xml path('')), 1, 1, '')
from Teams as tms

answered Mar 14 '11 at 13:14

Mikael Eriksson

136,425
22
210
281

Thank you Mikael Eriksson, It really worked for me this is what i wanted to execute exactly. Thanks – Manoj Mar 15 '11 at 13:53

SQL subquery return multiple values

1 Answers1