Ismail Sensei Ismail Sensei - 25 days ago 10
SQL Question

Reversed group by with multiple columns?

Hi i'm not sure if this possible in oracle database or any one but is possible to make this:

What i have:

Document | Volume | BAC | CO
-----------|-----------|---------|---------
TA1 | 4 | 2 | 0


What i want:

Document | Volume | BAC | CO | ID
-----------|-----------|---------|---------|---------
TA1 | 1 | 0 | 0 | 1
TA1 | 1 | 0 | 0 | 2
TA1 | 1 | 0 | 0 | 3
TA1 | 1 | 0 | 0 | 4
TA1 | 0 | 1 | 0 | 5
TA1 | 0 | 1 | 0 | 6


I tried using
WITH
but it's just mess in my
Sqldevelopper
now couldn't even come close to it knowing that
WITH
can't be used twice or been in
UNION
.

PS: Number of rows need to be equal to (Volume + Bac + CO).

Is this operation possible in ORACLE 12?

Answer

This should work and it only goes over the data once. It's a simple application of hierarchical query.

I added more test data; note that in the case of TA3, there should be no rows in the output (because all three values in the row are 0).

with
     test_data ( document, volume, bac, co ) as (
       select 'TA1', 4, 2, 0 from dual union all
       select 'TA2', 0, 0, 1 from dual union all
       select 'TA5', 0, 0, 0 from dual
     )
-- end of test data; actual solution (SQL query) begins below this line
select document,
       case when level <= volume                           then 1 else 0 end as volume,
       case when level >  volume and level <= volume + bac then 1 else 0 end as bac,
       case when level >  volume + bac                     then 1 else 0 end as co,
       level as id
from   test_data
where  volume + bac + co > 0
connect by level <= volume + bac + co
    and prior document = document
    and prior sys_guid() is not null
order by document, id    --  ORDER BY is optional
;

DOC     VOLUME        BAC         CO         ID
--- ---------- ---------- ---------- ----------
TA1          1          0          0          1
TA1          1          0          0          2
TA1          1          0          0          3
TA1          1          0          0          4
TA1          0          1          0          5
TA1          0          1          0          6
TA2          0          0          1          1

 7 rows selected 
Comments