OraFAQ Forum: Training & Certification » Transforming rows into comma separated output

Home » Other » Training & Certification » Transforming rows into comma separated output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Transforming rows into comma separated output [message #273517]

Wed, 10 October 2007 14:25

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!

Report message to a moderator

Re: Transforming rows into comma separated output [message #273518 is a reply to message #273517]

Wed, 10 October 2007 14:28

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Transforming rows into comma separated output [message #273525 is a reply to message #273518]

Wed, 10 October 2007 14:43

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.

Report message to a moderator

Re: Transforming rows into comma separated output [message #273526 is a reply to message #273517]

Wed, 10 October 2007 14:44

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: Transforming rows into comma separated output [message #273529 is a reply to message #273526]

Wed, 10 October 2007 14:47

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

Report message to a moderator

Re: Transforming rows into comma separated output [message #273533 is a reply to message #273529]

Wed, 10 October 2007 14:53

Michel Cadot
Messages: 68641
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

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

Report message to a moderator

Re: Transforming rows into comma separated output [message #273591 is a reply to message #273537]

Thu, 11 October 2007 01:09

Maaher
Messages: 7065
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

Report message to a moderator

Re: Transforming rows into comma separated output [message #273604 is a reply to message #273537]

Thu, 11 October 2007 01:21

Michel Cadot
Messages: 68641
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

Report message to a moderator

Previous Topic:	D2k Interview Questonsi
Next Topic:	Oracle placement questions(require answers)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 00:41:06 CDT 2024