OraFAQ Forum: Training & Certification » Insert Column Dynamically to a Table from another one

Home » Other » Training & Certification » Insert Column Dynamically to a Table from another one

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

Insert Column Dynamically to a Table from another one [message #287036]

Mon, 10 December 2007 23:59

saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka

Junior Member

Dear Friends

I have data Like
ID time
01 08:00

02 08:10

03 08:11

04 08:15

01 17:00

01 17:01

02 17:05
---------------------------------------------
I need like this
id time1 time2 time3
01 08:00 17:00 17:01
02 08:10 17:05
03 08:11
04 08:15

Is it possible? If yes then any hints will be appreciated. Thanks in advance.

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287061 is a reply to message #287036]

Tue, 11 December 2007 00:48

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Same question with different user.
http://www.orafaq.com/forum/m/287011/93410/#msg_287011

Did you search for "pivot" query on forms

[Updated on: Tue, 11 December 2007 01:15]

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287070 is a reply to message #287061]

Tue, 11 December 2007 01:00

Maaher
Messages: 7065
Registered: December 2001

Senior Member

It is possible: link

MHE

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287272 is a reply to message #287036]

Tue, 11 December 2007 15:08

saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka

Junior Member

I have solved the problem Smile

.
Thanks to all specially Maheer for his excellent demonstration about pivoting.

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287306 is a reply to message #287036]

Tue, 11 December 2007 22:19

saifurshaon
Messages: 29
Registered: December 2007
Location: Dhaka

Junior Member

I have solved my problem but still there remains a restriction.
I want to view the data for unlimited(more than 20) columns. How can I do that? In Maaher's demonstration he have worked for five columns.Can it be made dynamic??

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287349 is a reply to message #287306]

Wed, 12 December 2007 01:07

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As always you can get more answers if you:
- post a test case (create table and insert statements)
- format the code (OraFAQ Forum Guide)

There are several ways to do it. One is given at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#51542758465483

Regards
Michel

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287350 is a reply to message #287306]

Wed, 12 December 2007 01:09

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Not really dynamic, no. That's the problem with SQL: you need to know in advance what you select. The second solution (concatenation) might offer a way out but you might hit the VARCHAR2 size limit.

Even a REF cursor construction doesn't really help here as you'd probably have difficulties with handling the unknown number of columns.

If you were to use a REF cursor (using dynamic SQL), you would first need to count the maximum number of columns you'd be constructing. Then you'd construct the SQL and use that to open a weak REF cursor type. But does your front end know how to handle it?

MHE

Report message to a moderator

Re: Insert Column Dynamically to a Table from another one [message #287356 is a reply to message #287350]

Wed, 12 December 2007 01:27

Frank
Messages: 7901
Registered: March 2000

Senior Member

Maaher makes a very good point here. How do you plan on processing this query with a dynamic (even unlimited!) number of columns?
Are you sure that the number of columns will not be within a given range?
Time to review the design to get the actal needs.

Report message to a moderator

Previous Topic:	How many parameter u can pass in a procedure
Next Topic:	Procedure Vs Pakage

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 17:57:43 CST 2024