1

SQL newbie here.

I am trying to return a table with a list of memberships that belong to each user. The data is in two tables, one a table of users with name and description, and the other table with one or more records for each user with the name and membership identifier:

USER TABLE
Name   Description
------ -----------
User01 First User
User02 Second User

MEMBERSHIP TABLE
Name   Membership
------ ----------
User01 Group01
User01 Group02
User02 Group01
User02 Group03
User02 Group05

So far the code I have returns one line for each membership the user has:

SELECT     U.name, U.description, M.groupname
FROM         user U, membership M
WHERE     U.name = M.username
ORDER BY U.name


Name   Description Membership
------ ----------- ----------
User01 First User  Group01
User01 First User  Group02
User02 Second User Group01
User02 Second User Group03
User02 Second User Group05

I want to return a list of users, followed by all the groups that user belongs to:

Name   Description Membership
------ ----------- ----------
User01 First User  Group01, Group02
User02 Second User Group01, Group03, Group05

How do I get this kind of result ?

Thank you !

HandyRandyIndy
  • 11
  • 4
  • 1
    This is aggregate string concatenation, and is the subject of many questions. Google for your database and "aggregate string concatenation" and you'll find the solution. – Gordon Linoff Jan 31 '14 at 19:22
  • Possibly duplicate of --> http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings – Amir Keshavarz Jan 31 '14 at 20:26

0 Answers0