Home » Other » Training & Certification » Insert Column Dynamically to a Table from another one
Insert Column Dynamically to a Table from another one [message #287036] Mon, 10 December 2007 23:59 Go to next message
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.
Re: Insert Column Dynamically to a Table from another one [message #287061 is a reply to message #287036] Tue, 11 December 2007 00:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is possible: link

MHE
Re: Insert Column Dynamically to a Table from another one [message #287272 is a reply to message #287036] Tue, 11 December 2007 15:08 Go to previous messageGo to next message
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.
Re: Insert Column Dynamically to a Table from another one [message #287306 is a reply to message #287036] Tue, 11 December 2007 22:19 Go to previous messageGo to next message
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??
Re: Insert Column Dynamically to a Table from another one [message #287349 is a reply to message #287306] Wed, 12 December 2007 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Insert Column Dynamically to a Table from another one [message #287350 is a reply to message #287306] Wed, 12 December 2007 01:09 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Insert Column Dynamically to a Table from another one [message #287356 is a reply to message #287350] Wed, 12 December 2007 01:27 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: How many parameter u can pass in a procedure
Next Topic: Procedure Vs Pakage
Goto Forum:
  


Current Time: Wed Dec 07 11:02:23 CST 2016

Total time taken to generate the page: 0.05946 seconds