CODEWITHSUNDEEP
t-sql
Manoj
Manoj

Reputation: 1

SQL subquery return multiple values

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

Upvotes: 0

Views: 1754

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 2

Related Questions