0

Consider a database table connectouser holding two IDs:

 connectouser
compID    coopID
   1         1
   1         2
   1         3
   2         1
   2         2

Consider another database table coop holding two IDs:

coop
ID   Name
1     ABC
2     DEF
3     GHJ

I want to the following output :

Result
compID   coopname
1        ABC,DEF,GHJ
2        ABC,DEF

Can anyone please help me on this.

Hatim Ranapurwala
  • 103
  • 1
  • 15
  • Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) – Raymond Nijland Jun 04 '18 at 11:50

1 Answers1

1

The question was tagged MySQL for this answer.

This is a group by and group_concat():

select cu.compId, group_concat(co.name order by co.id) as coopnames
from connectouser cu join
     coop co
     on cu.coopID = co.ID
group by cu.compId;

In SQL Server, you can do:

select cu.compId,
       stuff( (select ',' + co.name
               from coop co
               where cu.coopID = co.ID
               order by co.id
               for xml path ('')
              ), 1, 1, ''
            ) as names
from connectouser cu;

The most recent version of SQL Server supports string_agg(), which is a much better approach.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786