asked on

Access 2003 Using one column to return multiple values

I need the sql for the following which has to work on access 2003.

I have 2 tables

List1 and Lookup1

List1 contains user_name and role_description
Lookup1 contains role_id and role_descrition.

There can be multiple roles for a user
I need to produce a list in the format.

user_name, role_id,role_id,role_id..etc..
eg.
FRED,1,2,4,6,8

The List1 table contains
FRED,role1
FRED,role2
FRED,role3
JOHN,role2
JOHN,role4

The lookup contains
1, role1
2, role2
3, role3
4, role4

The output needs to look like

FRED,1,2,3
JOHN,2,4

GRayL

TRANSFORM First(b.ID)
SELECT a.Name FROM List1 a INNER JOIN Lookup1 b ON a.role_description = b.role_description
GROUP BY a.Name
PIVOT a.role_description;

ericdavidsonca

ASKER

Hi GRayL

I needed to be clearer in my example.

The format should be.
Column1 Column2
FRED 1,2,3
JOHN 3,4

where all the role_id's are all in column 2 in asceding role_id order

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial