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? — Martin Smith
– Martin Smith, Commented Mar 14, 2011 at 11:11

Mikael Eriksson · Accepted Answer · 2011-03-14 13:14:27Z

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, 2011 at 13:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manoj Over a year ago

Thank you Mikael Eriksson, It really worked for me this is what i wanted to execute exactly. Thanks

Collectives™ on Stack Overflow

SQL subquery return multiple values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related