2 Replies Latest reply on Jul 12, 2018 4:24 AM by mikeTel

    Apex 5.0 :   two level pivot row, with row total

    mikeTel

      Hello Gurus,

       

      Got the following data: (thanks to fac586 for previous answer in separate case)

       

      with

         dgrid as (

                   select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 2 mcount from dual union

                   select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 4 mcount from dual union

                   select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 6 mcount from dual union

                   select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 12 mcount from dual union

                   select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 8 mcount from dual

                   ),

         cgrid as (

                   select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 10 ccount from dual union

                   select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 20 ccount from dual union

                   select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 30 ccount from dual union

                   select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 5 ccount from dual union

                   select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 40 ccount from dual

                   ),

      select

          *

      from

          (select

                coalesce(mcode, 'Total')     mcode

              , coalesce(week_start, date '9999-12-31') week_start

              , sum(mcount)                  mcount

          from

                dgrid

          group by

                grouping sets(

                    (mcode, week_start)

                  , (mcode)

                  , (week_start)

                  , ()))

      pivot (

          sum(mcount) for week_start in (date '2018-04-30' "30/04/18", date '2018-05-28' "28/05/18", date '2018-06-11' "11/06/18", date '2018-07-02' "02/07/18", date '9999-12-31' "Total")

      )

       

      What I want to achieve  this time is this :  (two level pivot row?)

       

      Screen Shot 2018-07-11 at 08.52.29.png

       

      Thanks in advance.

        • 1. Re: Apex 5.0 :   two level pivot row, with row total
          fac586

          mikeTel wrote:

           

          Got the following data: (thanks to fac586 for previous answer in separate case)

           

          with

          dgrid as (

          select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 2 mcount from dual union

          select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 4 mcount from dual union

          select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 6 mcount from dual union

          select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 12 mcount from dual union

          select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 8 mcount from dual

          ),

          cgrid as (

          select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 10 ccount from dual union

          select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 20 ccount from dual union

          select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 30 ccount from dual union

          select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 5 ccount from dual union

          select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 40 ccount from dual

          ),

          select

          *

          from

          (select

          coalesce(mcode, 'Total') mcode

          , coalesce(week_start, date '9999-12-31') week_start

          , sum(mcount) mcount

          from

          dgrid

          group by

          grouping sets(

          (mcode, week_start)

          , (mcode)

          , (week_start)

          , ()))

          pivot (

          sum(mcount) for week_start in (date '2018-04-30' "30/04/18", date '2018-05-28' "28/05/18", date '2018-06-11' "11/06/18", date '2018-07-02' "02/07/18", date '9999-12-31' "Total")

          )

           

          What I want to achieve this time is this : (two level pivot row?)

           

          Screen Shot 2018-07-11 at 08.52.29.png

          The SQL seems pretty obvious:

           

          with
            dgrid as (
                      select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 2 mcount from dual union
                      select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 4 mcount from dual union
                      select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 6 mcount from dual union
                      select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 12 mcount from dual union
                      select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 8 mcount from dual
                      ),
            cgrid as (
                      select 'CODE-04' mcode, date '2018-07-02' week_start, 520 location, 10 ccount from dual union
                      select 'CODE-04' mcode, date '2018-05-28' week_start, 520 location, 20 ccount from dual union
                      select 'CODE-05' mcode, date '2018-06-11' week_start, 520 location, 30 ccount from dual union
                      select 'CODE-05' mcode, date '2018-07-02' week_start, 520 location, 5 ccount from dual union
                      select 'CODE-05' mcode, date '2018-04-30' week_start, 520 location, 40 ccount from dual
                      )
          select
              *
          from
              (select
                    'D' src
                  , coalesce(mcode, 'Total')    mcode
                  , coalesce(week_start, date '9999-12-31') week_start
                  , sum(mcount)                  mcount
              from
                    dgrid
              group by
                    grouping sets(
                        (mcode, week_start)
                      , (mcode)
                      , (week_start)
                      , ())
              union all
              select
                    'C' src
                  , coalesce(mcode, 'Total')    mcode
                  , coalesce(week_start, date '9999-12-31') week_start
                  , sum(ccount)                  mcount
              from
                    cgrid
              group by
                    grouping sets(
                        (mcode, week_start)
                      , (mcode)
                      , (week_start)
                      , ()))
          pivot (
                  sum(case src when 'D' then mcount end) d
                , sum(case src when 'C' then mcount end) c
              for week_start in (date '2018-04-30' "30/04/18", date '2018-05-28' "28/05/18", date '2018-06-11' "11/06/18", date '2018-07-02' "02/07/18", date '9999-12-31' "Total")
          )
          order by
              mcode
          

           

          Create a custom report template to display the multi-level column headers in the APEX report. If you need help with this, create an example in a workspace on apex.oracle.com and share a guest developer login here.