Home » SQL & PL/SQL » SQL & PL/SQL » Collections (Oracle 9i)
Collections [message #337102] Tue, 29 July 2008 22:26 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
Am having datas in collection.for instance the values are like
cid c1 c2 c3
x 1 2 3
y 11 12 13
x 1 2 3

I would like to insert into a table like

cid c
x 1
x 2
x 3
y 11
y 12
y 13


How to do this.Kindly suggets.






Re: Collections [message #337104 is a reply to message #337102] Tue, 29 July 2008 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


Re: Collections [message #337105 is a reply to message #337102] Tue, 29 July 2008 22:39 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Do you mean you have the original data in memory-structures, like PL/SQL tables, or is it stored in a database-table with the structure given?
An easy way to split the data from one database table into the other would be to use a union all, although this would mean three FTS's, so it could be lengthy if your original table is very large.
insert into new_table
( cid
, c)
( select cid
  ,      c1
  from   original_table
  union all
  select cid
  ,      c2
  from   original_table
  union all
  select cid
  ,      c3
  from   original_table
)
Re: Collections [message #337137 is a reply to message #337102] Wed, 30 July 2008 01:04 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Join your table with a "row generator" of 3 lines and display the appropriate column accordingly.

More if you post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Previous Topic: ORA-01403: no data found + During Trigger Execution.
Next Topic: unique identifier..
Goto Forum:
  


Current Time: Thu Feb 06 12:52:17 CST 2025