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

Question

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.

have you looked into pivot tables/transforms?

mcalex
– mcalex

2012-10-21 12:04:23 +00:00
Commented Oct 21, 2012 at 12:04 — mcalex
– mcalex, Commented Oct 21, 2012 at 12:04

Oleksandr Fedorenko · Accepted Answer · 2012-10-23 07:42:12Z

3

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

edited Oct 23, 2012 at 7:42

answered Oct 21, 2012 at 13:58

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

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

4 Comments

Oleksandr Fedorenko Over a year ago

@talkpoppycock Why.I checked this script

Andriy M Over a year ago

@talkpoppycock: Are you using SQL Server 2005+?

Andriy M Over a year ago

@AlexanderFedorenko: The join in the outer query is probably unnecessary.

talkpoppycock Over a year ago

Yes I am using SQL Server 2005

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related