Skip to Main Content
  • Questions

  • Split 1 row into 2 rows based on column values without UNION

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anupam.

Asked: April 06, 2018 - 5:18 pm UTC

Last updated: May 10, 2019 - 8:54 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hi,

I will be glad if you could help me to know if the below can be achieved without using UNION
I want to split a row into 2 based on a column value

create table xx_test_split (
id number,
amount number,
discount_amount number,
currency varchar2(3),
entity varchar2(10)
)


insert into xx_test_split values
(1001,100,0,'USD','ASSET');


insert into xx_test_split values
(1002,900,18,'USD','INFRA');

--

select * from xx_test_split;

--
IF discount_amount != 0 THEN we need the row to be split into 2 rows one for the normal Amount (type = LINE) and one for the Discount Amount (type = DISC)

ID AMOUNT ENTITY TYPE
-----------------------------
1002 USD INFRA LINE
1002 USD INFRA DISC

If discount_amount = 0 THE we just need the same row as type = LINE

ID AMOUNT ENTITY TYPE
-----------------------------
1001 USD ASSET LINE

--
So as a whole the query should extract

ID AMOUNT ENTITY TYPE
-----------------------------
1001 USD ASSET LINE
1002 USD INFRA LINE
1002 USD INFRA DISC



Thank You


and Connor said...

SQL> create table xx_test_split (
  2  id number,
  3  amount number,
  4  discount_amount number,
  5  currency varchar2(3),
  6  entity varchar2(10)
  7  );

Table created.

SQL>
SQL> insert into xx_test_split values
  2  (1001,100,0,'USD','ASSET');

1 row created.

SQL>
SQL> insert into xx_test_split values
  2  (1002,900,18,'USD','INFRA');

1 row created.

SQL> select * from xx_test_split;

        ID     AMOUNT DISCOUNT_AMOUNT CUR ENTITY
---------- ---------- --------------- --- ----------
      1001        100               0 USD ASSET
      1002        900              18 USD INFRA

SQL> select *
  2  from xx_test_split,
  3       ( select rownum seq from dual connect by level <= 2 )
  4  where ( discount_amount > 0 or seq = 1 );

        ID     AMOUNT DISCOUNT_AMOUNT CUR ENTITY            SEQ
---------- ---------- --------------- --- ---------- ----------
      1001        100               0 USD ASSET               1
      1002        900              18 USD INFRA               1
      1002        900              18 USD INFRA               2

SQL>

You can then add a CASE or DECODE around the selected columns to map the output however you like


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternative with UNPIVOT (not "better", just different)

Stew Ashton, April 08, 2018 - 8:04 am UTC 

select * from xx_test_split
unpivot(
  amount for type in (
    amount as 'LINE',
    discount_amount as 'DISCOUNT'
  )
)
where amount > 0;

        ID CUR ENTITY     TYPE         AMOUNT
---------- --- ---------- -------- ----------
      1001 USD ASSET      LINE            100
      1002 USD INFRA      LINE            900
      1002 USD INFRA      DISCOUNT         18

If DISCOUNT_AMOUNT were NULL instead of zero, then by default UNPIVOT would filter the NULL rows automatically.
update xx_test_split set discount_amount = null where discount_amount = 0;

select * from xx_test_split
unpivot(
  amount for type in (
    amount as 'LINE',
    discount_amount as 'DISCOUNT'
  )
);

        ID CUR ENTITY     TYPE         AMOUNT
---------- --- ---------- -------- ----------
      1001 USD ASSET      LINE            100
      1002 USD INFRA      LINE            900
      1002 USD INFRA      DISCOUNT         18

Best regards, Stew Ashton

Lateral in 12c

Rajeshwaran, Jeyabal, April 10, 2018 - 1:48 pm UTC

Using Lateral in Oracle 12c.
demo@ORA12C> select id,amount,discount_amount,currency,entity,
  2        decode(r,1,'Line','Disc') as types
  3  from xx_test_split , lateral( select level r
  4              from dual
  5              connect by level <= decode(discount_amount,0,1,2) )
  6  /

        ID     AMOUNT DISCOUNT_AMOUNT CUR ENTITY     TYPE
---------- ---------- --------------- --- ---------- ----
      1001        100               0 USD ASSET      Line
      1002        900              18 USD INFRA      Line
      1002        900              18 USD INFRA      Disc

and using Table/cast/multiset prior to that.
demo@ORA12C> select id,amount,discount_amount,currency,entity,
  2        decode(column_value,1,'Line','Disc') as types
  3  from xx_test_split , table( cast( multiset( select level r
  4        from dual
  5        connect by level <= decode(discount_amount,0,1,2) )
  6        as sys.odcinumberlist) ) t2
  7  /

        ID     AMOUNT DISCOUNT_AMOUNT CUR ENTITY     TYPE
---------- ---------- --------------- --- ---------- ----
      1001        100               0 USD ASSET      Line
      1002        900              18 USD INFRA      Line
      1002        900              18 USD INFRA      Disc

demo@ORA12C>

Connor McDonald
April 11, 2018 - 3:29 am UTC

Good to see you back

A reader, April 10, 2018 - 10:17 pm UTC


5 mins

A.Srinivas Rao, May 02, 2019 - 9:32 am UTC

Hi,

I have a row like

Id From To Amount

8 01/04/2018 31/03/2019 4315

Now I need the out put as

Id From To Amount

8 01/04/2018 31/03/2019 2158

8 01/04/2018 31/03/2019 2158

That amount column should be split into exactly two rows(Two half).

Its an urgent requirement for me. Any query would be appreciated.



Chris Saxon
May 10, 2019 - 8:54 am UTC

What exactly have you tried? Surely this is just a matter of plugging your tables into Connor's solution and dividing the amount by 2?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.