Home » Other » Training & Certification » Transforming rows into comma separated output
Transforming rows into comma separated output [message #273517] Wed, 10 October 2007 14:25 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi,

I have a table which has data structure like below:

Quote:


Name--------Template----Flag
===============================
Radio ------Electronics---1
CDPlayer----Electronics---1
VCD---------Electronics---1
Chair-------Furniture-----1
Sofa--------Furniture-----0
Bed---------Furniture-----0



Now my requirement is that I need a PL/SQL to check for the data in the table with Flag = 1 and display it in the below format:

Quote:


Electronics=Radio,CDPlayer,VCD
Furniture=Chair



Is this possible using the PL/SQL?

Please let me know. This one is really urgent. Anly help will be highly appreciated.

Thanks!
Re: Transforming rows into comma separated output [message #273518 is a reply to message #273517] Wed, 10 October 2007 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>This one is really urgent.
So exactly when is this homework assignment due?

Post what you done so far.
Re: Transforming rows into comma separated output [message #273525 is a reply to message #273518] Wed, 10 October 2007 14:43 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Well ... I wont name it as "due Homework" Smile ... was looking at different possibilities to generate the files from the DB data.

I was able to create the file as is from the table i.e. row by row.

Like in below example it will be:

Radio,Electronics
CDPlayer,Electronics
VCD,Electronics
Chair,Furniture

But what I was wondering if i can change the format to below one for better understanding.

Electronics=Radio,CDPlayer,VCD
Furniture=Chair

I must tell in advance that I'm pretty new to Oracle. Smile and thats why i really can not differentiate between the Newbie question and the Expert question. Sorry for that Confusion!


Please let me know if you can help.
Re: Transforming rows into comma separated output [message #273526 is a reply to message #273517] Wed, 10 October 2007 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Question asked many times as teachers don't have so much imagination to create exercise.
So search or at least post what you already tried.

Regards
Michel
Re: Transforming rows into comma separated output [message #273529 is a reply to message #273526] Wed, 10 October 2007 14:47 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi Michel,

I searched the forum for about an hour before posting this one.

I wouldnt have done this .. if I would have got any hint from anywhere during that one hour.

I thought .. probably i can get some hint from here Sad
Re: Transforming rows into comma separated output [message #273533 is a reply to message #273529] Wed, 10 October 2007 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "pivot" or "rows to columns" or "stragg" or "concat_all".

Regards
Michel

[Updated on: Wed, 10 October 2007 14:54]

Report message to a moderator

Re: Transforming rows into comma separated output [message #273537 is a reply to message #273533] Wed, 10 October 2007 15:13 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks Michel,

But all these functions are user built ... what i was looking for is any in-built oracle functionality which can do the same job ...

I already looked @ these functions before starting the post.

I'll try to implement it using the cursors ... anyways thanks for the help ... and putting this post into the "homework,training and certification" section without analyzing it ...


Re: Transforming rows into comma separated output [message #273591 is a reply to message #273537] Thu, 11 October 2007 01:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you're looking for a built-in solution. How about SYS_CONNECT_BY_PATH? I'd look at this thread for example. I'm pretty sure you'll find more examples here. Joe Fuda has summarized different techniques at his website but I let you search for it Very Happy.

MHE
Re: Transforming rows into comma separated output [message #273604 is a reply to message #273537] Thu, 11 October 2007 01:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it smells homework and as you didn't post any information about your searches nor your tries nor your Oracle version, there is nothing to analyze. It definitively smells homework on your first post.

Regards
Michel
Previous Topic: D2k Interview Questonsi
Next Topic: Oracle placement questions(require answers)
Goto Forum:
  


Current Time: Sun Dec 04 16:33:33 CST 2016

Total time taken to generate the page: 0.14278 seconds