INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

CODE -->

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:
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 


Below Tables are used:
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 

Please guide/input/suggest/help/assist.

Thanks,
Ken

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

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: How to concatenate multiple child records into a single value

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

(OP)
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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close