CODEWITHSUNDEEP
sqlsql-server
talkpoppycock
talkpoppycock

Reputation: 147

SQL combining data from multiple rows in two tables into one row

I have data from two tables in a members system as follows:

Table: listuser
userid  listid
===========
1       5
1       8
1       3
2       5
2       3
3       4
4       4
5       3
6       4

Table: list
id  name
==============
3   Members
4   Non Members
5   Subscribers
8   Test

What I am trying to achieve is a result as follows:

userid  lists
===================
1       Members, Subscribers, Test,
2       Members, Subscribers
3       Non Members,
4       Non Members,
5       Members,
6       Non Members,

Users can belong between 0 and n lists. I have tried a number of approaches, but always end up with multiple rows for userid which is what I am trying to eliminate. I would be very great full for some help.

Upvotes: 3

Views: 2012

Answers (1)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

Try this. It will help you

SELECT DISTINCT lu2.userid,(
SELECT ISNULL(l1.name, '') + ', '
FROM dbo.listuser lu1 JOIN dbo.list l1 ON lu1.listid = l1.id
WHERE lu1.userid = lu2.userid 
FOR XML PATH('')) AS name
FROM dbo.listuser lu2
ORDER BY lu2.userid

Example on SQL Fiddle

Upvotes: 3

Related Questions