Members   Search   Help    Register    Login   Home
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.
Report message to a moderator
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: 5371
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
Report message to a moderator
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.
Report message to a moderator
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: 16
Registered: June 2008
Location: chennai
Junior 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.
Report message to a moderator
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,
Report message to a moderator
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: 7317
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.
Report message to a moderator
Previous Topic:Remove primary key
Next Topic:display columns names from all_tab_columns with primary key on top rows
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 1 20:55:08 CST 2009

Total time taken to generate the page: 0.06095 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.