Oracle SQL data fetch order

Question

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 1

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

Answer 2

I think what may work for you is to make another column in your query, that is col_1 || sum(of all at that col_1 value).

So all rows with col_1 = A would have this column be 'A 4' or something like 'Sum of all A is 4' whatever you want.

Then display this in an interactive report, and set a break on this new column.

Then you will have the query neatly sorted into blocks with different col_1 values and the sum of the blocks under that value next to it.

This will require the rewrite of the query, actually just a small addition to it. But we cant help with that since you did not post the query.

EDIT:

Now that you added the query I noticed another thing you might be able to do

SELECT * FROM(
    select  col_1 as col_0
           ,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  col_1 as col_0
           ,'Sum '||col_1 as 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)
  ORDER BY col_0, col_1 desc

This is basically just adding another column that is the value of col_1, then you order by that so that you have all As and the Sum of A together, all Bs ... Then the order by col_1 is so that you ensure Sum of A comes before or after A depending if you choose ascending or descending.

If you then hide this first column in apex this will come out with what you are looking for.