how to convert column into rows?

user8731258 · Feb 21, 2010 8:15AM

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.

Satyaki_De · Feb 21, 2010 8:24AM

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

21205 · Feb 21, 2010 8:28AM

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

user8731258 · Feb 21, 2010 9:25AM

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?

Satyaki_De · Feb 21, 2010 9:47AM

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.

user8731258 · Feb 23, 2010 7:48PM

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.

user8731258 · Feb 23, 2010 7:57PM

also i want to know that if i use a union all and one of thew queries returns null what happens?

Satyaki_De · Feb 24, 2010 12:57AM

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.

Aketi Jyuuzou · Feb 24, 2010 6:14AM

I like Pivot selecting multiTableInsert B-)
http://www.geocities.jp/oraclesqlpuzzle/8-52.html

insert 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;

Satyaki_De · Feb 24, 2010 6:21AM

That's cool.

In a sense, you have beautifully grafted this known command for this purpose and removes unwanted union all.

Regards.

Satyaki De.

user8731258 · Mar 1, 2010 10:52PM

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;

Forum Stats

Quick Links

Categories

how to convert column into rows?

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

how to convert column into rows?

Answers