Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve different data separated by comma into one column (Oracle 9i)
How to retrieve different data separated by comma into one column [message #429483] Wed, 04 November 2009 02:40 Go to next message
anusha539
Messages: 7
Registered: October 2009
Location: Hyderabad
Junior Member
Hi all,

I have a Customer table.In that some of the customer's Bill-To address is different remaining all columns contain same data.In that case if I query with that customer_number it displays more than one row with different Bill-to addresses,it is fine.But my requirement is I need to display only one row for that customer_number and Bill-to address column shows all Bill-to addresses corresponding to that customer separated by comma.

Could anyone give me any idea to solve this.


Thanks in Advance,
Anusha.
Re: How to retrieve different data separated by comma into one column [message #429484 is a reply to message #429483] Wed, 04 November 2009 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing that you want some way to concatenate the addresses from several rows onto one row.

Try looking at Tom Kyte's STRAGG
Re: How to retrieve different data separated by comma into one column [message #429488 is a reply to message #429484] Wed, 04 November 2009 03:17 Go to previous messageGo to next message
anusha539
Messages: 7
Registered: October 2009
Location: Hyderabad
Junior Member
HI JRowbottom,

Thank you so much for your reply.

Re: How to retrieve different data separated by comma into one column [message #429661 is a reply to message #429483] Thu, 05 November 2009 01:38 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Anusha


you can make a transpose table in which u can get the different addressinto single row for example
custmer1 with address1,address2,address3 etc and u can fetch from that table.
Re: How to retrieve different data separated by comma into one column [message #429684 is a reply to message #429661] Thu, 05 November 2009 03:56 Go to previous messageGo to next message
anusha539
Messages: 7
Registered: October 2009
Location: Hyderabad
Junior Member
Hi,

Thank you for your response.Actually in my requirement I need to display that Bill-to address field in Oracle Apps form.So I created a function to retrieve Bill-to address separated by comma.I have solved my issue Smile .

Thanks,
Anusha,
Re: How to retrieve different data separated by comma into one column [message #429687 is a reply to message #429661] Thu, 05 November 2009 04:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kish_ulike wrote on Thu, 05 November 2009 08:38
Anusha


you can make a transpose table in which u can get the different addressinto single row for example
custmer1 with address1,address2,address3 etc and u can fetch from that table.

This is the SQLServer/Sybase way of doing things. In Oracle you typically would not store intermediate results in tables.
Previous Topic: Remove primary key
Next Topic: display columns names from all_tab_columns with primary key on top rows
Goto Forum:
  


Current Time: Mon Dec 05 02:55:16 CST 2016

Total time taken to generate the page: 0.11325 seconds