Oracle SQL data fetch order

I’m trying to achieve this Layout. I’m not sharing the query because it’s working, I’m just wondering what functions to use in order to achieve format ‘2’. In the beginning I was doing UNION ALL , that’s how I got format ‘1’. The data is fetched from multiple tables. I’m trying to achieve this without PLSQL. Thank you !

How I got it ‘1’

    +-----+----+---+-----+
    |col_1|col2|...|col_n|
    +-----+----+---+-----+
    |  A  |bla |...+  3  |
    +-----+----+---+-----+
    |  A  |bla1|...|  1  |
    +-----+----+---+-----+
    |  B  |asd |...+  2  |
    +-----+----+---+-----+
    |  B  |qwe1|...|  6  |
    +-----+----+---+-----+
    |  B  |zxc1|...|  1  |
    +-----+----+---+-----+
    |Sum:A|    |...|  4  |
    +-----+----+---+-----+
    |Sum:B|    |...|  9  |
    +-----+----+---+-----+

How I want to transform it ‘2’

    +-----+----+---+-----+
    |col_1|col2|...|col_n|
    +-----+----+---+-----+
    |  A  |bla |...+  3  |
    +-----+----+---+-----+
    |     |bla1|...|  1  |
    +-----+----+---+-----+
    |Sum:A|    |...|  4  |
    +-----+----+---+-----+
    |  B  |asd |...+  2  |
    +-----+----+---+-----+
    |     |qwe1|...|  6  |
    +-----+----+---+-----+
    |     |zxc1|...|  1  |
    +-----+----+---+-----+
    |Sum:B|    |...|  9  |
    +-----+----+---+-----+

select 
        col_1
       ,col_2
       ...
       col_n
from(
    select 
        kce.name as col_1
       ,kcp.other_name as col_2
       ...
       ,irm.col_n
    from tab_1 irm
    left join tab_2 kce
    on irm.irm_s_id = kce.id
    left join tab_3 kcp
    on irm.irm_p_id = kcp.id
    where irm.customer = :P1_GROUP
    order by irm.irm_s_id,irm.irm_p_id
)
union all
select 'Sum '||col_1
       ,null
       ...
       ,sum(col_n)
from(
    select 
        kce.name as col_1
       ,kcp.other_name as col_2
       ...
       ,irm.col_n
    from tab_1 irm
    left join tab_2 kce
    on irm.irm_s_id = kce.id
    left join tab_3 kcp
    on irm.irm_p_id = kcp.id
    where irm.customer = :P1_GROUP
    order by irm.irm_s_id,irm.irm_p_id
) group by 'Sum '||col_1

Answer

APEX has built-in support for this type of formatting in Classic Reports – it’s called Break Formatting. Follow these steps to get an idea of how it works:

Create a Classic Report with the following query:

select job,
  ename,
  sal
from emp
order by job, sal

Go into the report options and set Break Columns (under Break Formatting) to First Column. Then, drill into the SAL column and enable the Sum setting.

That should give you something like this:

See this blog post by Jeff Eberhard for more info: https://blog.eberapp.com/ords/f?p=BLOG:READ:::::ARTICLE:6555300346233507&cs=17CDD85DFBD5E33D7BD8F7945B94027CE