Forum Stats

  • 3,837,640 Users
  • 2,262,276 Discussions
  • 7,900,337 Comments

Discussions

how to convert column into rows?

user8731258
user8731258 Member Posts: 434
edited Nov 4, 2010 9:02AM in SQL & PL/SQL
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.

Answers

  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    edited Feb 21, 2010 9:17AM
    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
    21205 Member Posts: 6,168 Gold Trophy
    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
    user8731258 Member Posts: 434
    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
    Satyaki_De Member Posts: 7,081
    edited 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
    user8731258 Member Posts: 434
    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
    user8731258 Member Posts: 434
    also i want to know that if i use a union all and one of thew queries returns null what happens?
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 4, 2010 9:02AM
    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
    Satyaki_De Member Posts: 7,081
    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
    user8731258 Member Posts: 434
    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.