How to use SQL to get multi-row to single column output

[Ken]

Hi,

Can only use Access SQL for below tables and after trying different queries, like crosstab giving different result.

One crosstab result appears with unique row with separate columns for itemref.

Select distinct cust_email, Item_ref 
from       UserList    UL
right join Product P on  UL.itemref = p.itemNo	  
order by cust_email, ItemRef

Looking forward to get output as below:
[pre]Output Required:
Cust_Name Cust_Login Cust_Email ItemRef
Me Chin Me.chin me.chin@gmail.com I209N
Ken Mark Ken Mark KenMark@gmail.com I209N
Jack Cole JackC JCole@gmail.com I546R,I381E
Tim Smith TSmith TimSmith@gmail.com I987N[/pre]


Below Tables are used:
[pre]Table: Product
ItemNo ItemName List
I201N Item_Nice A
I209N New Launch A
I315R Runner A
I987N Lead Matt B
I546R Climber C
I381R Chromite C


Table: UserList
Cust_Name Cust_Login Cust_Email ItemRef
Me Chin Me.chin me.chin@gmail.com I209N
Ki Chen KyeChen Kye_Chen@gmail.com I209N
Me Chin MeChin me.chin@gmail.com I209N
Tim Smith TSmith TimSmith@gmail.com I987N
Ken Mark Ken Mark KenMark@gmail.com I209N
Jack Cole JackC JCole@gmail.com I546R
Jack Cole JackC JCole@gmail.com I381E[/pre]

Please guide/input/suggest/help/assist.

Thanks,
Ken

 

[MajP]

Your tables make no sense. Why would you repeat Customer Name, Login, Cust Email? Is this information coming from an external source. Should be a customer table with one record per customer and a unique customer primary key. If login is unique you could use that or an autonumber. Should be a product table with a primary key. Item no would likely be good. Then you should have a customer_Product_link table. This would allow you to do a many to many. A customer can be related to many products and a product could be assoicated to many customers.

To concatenate see. FAQ701-4233

 

[Ken]

Hi Majp,

Data is external. Secondly, forget the days where one will get with some unique primary key. Nowadays, one need to work from the data that is available and find out primary key, create a table, process and provide the output.

In this case, yes the table received are as is for product and userlist.
From these 2 tables, we need to create unique output for each customer.

That is why, in SQL have added select distinct, to eliminate any duplicate.

Ken

 

[MajP]

Sorry. I posted the wrong link. I have updated to a better link which will run in a query but still not a pure SQL solution. If you want a pure SQL solution, it cannot be done in Access AFAIK. SQL Server and other databases can do this.