Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
how to convert column into rows?

user8731258
Member Posts: 434
Hi,
I have a table say a.IT has gt columns a_1,a_2,a_3.a_4.I have another table b.It has got only one column b.
Now i want to insert the 4 columns from table a into 4 rows of table b.
Can u please suggest the code.
I am using oracle 10g.
I have a table say a.IT has gt columns a_1,a_2,a_3.a_4.I have another table b.It has got only one column b.
Now i want to insert the 4 columns from table a into 4 rows of table b.
Can u please suggest the code.
I am using oracle 10g.
Answers
-
Something like this ->
[email protected]> [email protected]>select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production Elapsed: 00:00:00.01 [email protected]> [email protected]> [email protected]> --This is the data part -- [email protected]>with tt 2 as 3 ( 4 select 1 cola, 2 colb, 3 colc, 4 cold from dual 5 ) -- End Of Data Part -- 6 select cola 7 from tt 8 union all 9 select colb 10 from tt 11 union all 12 select colc 13 from tt 14 union all 15 select cold 16 from tt; COLA ---------- 1 2 3 4 Elapsed: 00:00:00.45 [email protected]> [email protected]>
So, your final query should look like this ->1 insert into target_tab 2 select * 3 from ( 4 select cola 5 from tt 6 union all 7 select colb 8 from tt 9 union all 10 select colc 11 from tt 12 union all 13 select cold 14 from tt 15 );
Regards.
Satyaki De.
Added Insert Part as op requires that.
Edited by: Satyaki_De on Feb 21, 2010 7:44 PM -
insert into b select a_1 from it; insert into b select a_2 from it; insert into b select a_3 from it; insert into b select a_4 from it;
would be one way -
Hi,
thanx for the reply.But if i have to insert these rows into thte table b(mentioned in the query) then how would it look?
with tt
as
(select a_1 col1,a_2 col2,a_3 col3,a_4 col4 from a)
insert into b
values
select a_1 from tt
union all
select a_2 from tt
union all
select a_3 from tt
union alla
select a_4 from tt
is this ok? -
You didn't follow me.
With clause is a temporary table for me as you didn't provide any data. So, to demonstrate i need to prepare one temporary tables and then produce the output.
For you the query should only look like this ->insert into <target_table> select * from ( select a_1 from <source_table> union all select a_2 from <source_table> union all select a_3 from <source_table> union alla select a_4 from <source_table> );
Regards.
Satyaki De. -
hi.. i have to insert columns from table a to table b.
A column in table b is the primary_key. It is populated through a sequence.When i have to insert the 4 mentioned columns from table a to table b ,i also have to populate the primary key say b_4_pk.How do i do it in the above mentioned query.?please reply asap. -
also i want to know that if i use a union all and one of thew queries returns null what happens?
-
Are you looking something like this?
satyaki> satyaki>select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production Elapsed: 00:00:00.04 satyaki> satyaki> satyaki>create sequence seq1 2 start with 1 3 increment by 1; Sequence created. Elapsed: 00:00:00.68 satyaki> satyaki>create table dest_a 2 ( 3 sr_no number(2) not null, 4 col number(4), 5 constraints pk_sr_no primary key(sr_no) 6 ); Table created. Elapsed: 00:00:01.17 satyaki> satyaki>create table src_a 2 ( 3 cola number(2), 4 colb number(2), 5 colc number(2), 6 cold number(2) 7 ); Table created. Elapsed: 00:00:00.00 satyaki> satyaki>insert into src_a values(1,2,3,4); 1 row created. Elapsed: 00:00:00.00 satyaki> satyaki>insert into src_a values(5,null,null,7); 1 row created. Elapsed: 00:00:00.00 satyaki> satyaki>commit; Commit complete. Elapsed: 00:00:00.01 satyaki> satyaki> satyaki>select * from src_a; COLA COLB COLC COLD ---------- ---------- ---------- ---------- 1 2 3 4 5 7 Elapsed: 00:00:00.03 satyaki> satyaki> satyaki>select * from dest_a; no rows selected Elapsed: 00:00:00.00 satyaki> satyaki> satyaki>insert into dest_a(sr_no,col) 2 select seq1.nextval, 3 x.* 4 from ( 5 select cola 6 from src_a 7 union all 8 select colb 9 from src_a 10 union all 11 select colc 12 from src_a 13 union all 14 select cold 15 from src_a 16 ) x; 8 rows created. Elapsed: 00:00:00.01 satyaki> satyaki>select * from dest_a; SR_NO COL ---------- ---------- 1 1 2 5 3 2 4 5 3 6 7 4 8 7 8 rows selected. Elapsed: 00:00:00.06 satyaki>
Regards.
Satyaki De. -
I like Pivot selecting multiTableInsert B-)
http://www.geocities.jp/oraclesqlpuzzle/8-52.htmlinsert all into target_tab values(cola) into target_tab values(colb) into target_tab values(colc) into target_tab values(cold) select cola,colb,colc,cold from tt;
-
That's cool.
In a sense, you have beautifully grafted this known command for this purpose and removes unwanted union all.
Regards.
Satyaki De. -
declare
v_seq_order number;
v_main_doc_no varchar2(100);
v_iss_airline varchar2(100);
v_sector_no number;
v_file_id staging_ticket_main.file_id%type;
begin
select seq_order,main_doc_no,iss_airline into v_seq_order,v_main_doc_no,v_iss_airline from staging_ticket_main
WHERE file_id =2804
AND iss_airline=738
AND main_doc_no=2450508386;
select sector_no_seq.nextval into v_sector_no from dual;
insert into staging_ticket_sector
(seq_order,
file_id,
conj_doc_no,
iss_airline,
main_doc_no,
sector_no,
coupon_no,
from_city,
to_city,
carrier_audit_cpn,
flight_no,
class,
flight_date,
flight_time,
orig_fare_basis,
not_valid_before,
not_valid_after,
stopover_ind)
select
v_seq_order,
v_file_id,
v_iss_airline,
v_main_doc_no,
v_sector_no,tt.*
from
(SELECT XL_CNR_COUPON_NUM_1,
XL_COC_ORIGIN_CITY_1,
XL_CDC_DESTINATION_CITY_1,
XL_CAC_AIRLINE_CODE_1,
XL_CFN_FLIGHT_NUM_1,
XL_CBD_BOOKING_DES_1,
XL_CFD_FLIGHT_DATE_1,
XL_CFT_FLIGHT_TIME_1,
XL_CFB_FARE_BASE_1,
XL_CBE_NOT_VALID_B4_1,
XL_CAF_NOT_VALID_AFTER_1,
XL_CSO_STOPOVER_IND_1
FROM xl_tcn_record
WHERE xl_tkc_plating_carrier =738
AND xl_tkd_ticket_sequence =2450508386
AND rownum =1)tt;
end;
--end;
This discussion has been closed.