Forum Stats

  • 3,770,408 Users
  • 2,253,104 Discussions
  • 7,875,445 Comments

Discussions

Split Single Row into Multiple Rows

AFF-MMCL
AFF-MMCL Member Posts: 4 Red Ribbon
edited Oct 30, 2019 8:11AM in SQL & PL/SQL

Hello,

I have a requirement Where I want to Convert data in first table into second table. How Can I do this using sql/pl/sql. First Table is also Custom and Second table is also Custom.

Emp Code
Emp Name
Desgination
Period1Basic Salary 1Gross Salary 1
House Rent 1Period2Basic Salary 2Gross Salary 2House Rent 2
WS100JohnConsultantJan-191000015000500Feb-191400018000700

MonthYearEmp CodeEmp NameDesignationBasic SalaryGross SalaryHouse Rent
Jan19WS100JohnConsultant1000015000500
Feb19WS100JohnConsultant1400018000700

Thanks and Regards,

Saad Qaiser Khan

Tagged:

Answers

  • MAhmad
    MAhmad Member Posts: 13
    edited Oct 30, 2019 3:58AM

    Hi,

    You can use UNION to split a row into two rows and insert into table2

    I taken two tables TEST1 and TEST2. Fetch a row from table TEST1 and insert two rows into TEST2

    4098823_reply.JPG

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Oct 30, 2019 5:42AM

    Hi,

    You can use SELECT … UNPIVOT; it's much more efficient (and less coding) than UNION.

    Try something like this in an INSERT or MERGE statement:

    SELECT    TO_CHAR (period, 'Mon')   AS month,         TO_CHAR (period, 'YYYY')  AS year,         emp_code,         emp_name,         designation,         basic_salary,         gross_salary,         house_rentFROM      table_xUNPIVOT   (    basic_salary, gross_salary, house_rent)          FOR  ( (basic_salary_1, gross_salary_1, house_rent_1)  AS 1               , (basic_salary_2, gross_salary_2, house_rent_2)  AS 2               )          );

    If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

    See the Forum FAQ:

  • BluShadow
    BluShadow Member, Moderator Posts: 41,496 Red Diamond
    edited Oct 30, 2019 8:11AM

    Or the old fashioned way, before pivot and unpivot was available, using row generation...

    SQL> with t(emp_code, emp_name, designation, period1, basicsal1, grosssal1, houserent1, period2, basicsal2, grosssal2, houserent2) as (
      2    select 'WS100', 'John', 'Consultant', date '2019-01-01', 10000, 15000, 500, date '2019-02-01', 14000, 18000, 700 from dual
      3    )
      4  select to_char(case when rn=1 then period1 else period2 end,'Mon') as mn
      5        ,extract(year from case when rn=1 then period1 else period2 end) as yr
      6        ,emp_code
      7        ,emp_name
      8        ,designation
      9        ,case when rn=1 then basicsal1 else basicsal2 end as basicsal
    10        ,case when rn=1 then grosssal1 else grosssal2 end as grosssal
    11        ,case when rn=1 then houserent1 else houserent2 end as houserent
    12  from  t
    13        cross join (select level as rn from dual connect by level <= 2)
    14  /MN          YR EMP_C EMP_ DESIGNATIO  BASICSAL  GROSSSAL  HOUSERENT
    --- ---------- ----- ---- ---------- ---------- ---------- ----------
    Jan      2019 WS100 John Consultant      10000      15000        500
    Feb      2019 WS100 John Consultant      14000      18000        700SQL>