create table data_row
(
col1 varchar2(30)
,col2 varchar2(20)
,col1n number
,col2n number
,col3c varchar2(20)
)Table created.
insert into data_row
values ('plant','cactus',3,4,'special plant')1 row(s) inserted.
insert into data_row
values ('plant','mint',3,2,'plant')1 row(s) inserted.
select *
from data_row| COL1 | COL2 | COL1N | COL2N | COL3C | plant | cactus | 3 | 4 | special plant | plant | mint | 3 | 2 | plant |
|---|
Split SQL
with
split_row as ( select 1 as rn from dual union all select 2 as rn from dual
)
select *
from(
SELECT col1
,col2
,col1n
, case
when col2n>col1n THEN 'Y'
else 'N'
end as split
, split.rn
, case
when col2n>col1n and split.rn =2
THEN col2n-col1n
ELSE col1n
end as col2n
, case
when col2n>col1n and split.rn =2
THEN col3c
ELSE null
end as col3c
FROM data_row data_row
join split_row split on split.rn in (1,2)
) s
where s.rn=1 OR (s.rn=2 and s.split='Y')| COL1 | COL2 | COL1N | SPLIT | RN | COL2N | COL3C | plant | cactus | 3 | Y | 1 | 3 | - | plant | mint | 3 | N | 1 | 3 | - | plant | cactus | 3 | Y | 2 | 1 | special plant |
|---|
Split SQL 2nd attempt
with
split_row as ( select 1 as rn from dual union all select 2 as rn from dual
)
select *
from data_row
where col2n <=col1n
UNION ALL
SELECT col1
,col2
,col1n
, case
when split.rn =2
THEN col2n-col1n
ELSE col1n
end as col2n
, case
when split.rn =2
THEN col3c
ELSE null
end as col3c
FROM data_row data_row
join split_row split on split.rn in (1,2)
where col2n > col1n| COL1 | COL2 | COL1N | COL2N | COL3C | plant | mint | 3 | 2 | plant | plant | cactus | 3 | 3 | - | plant | cactus | 3 | 1 | special plant |
|---|