Search code examples
sqloracle-databasepivotrollupdynamic-columns

SQL pivot table total per column

So I have this table that has dynamic headers.

enter image description here

The query below is used to generate the dates that will be used for the table's query.

select listagg(INSERT_DATE,
''',''') WITHIN GROUP(ORDER BY INSERT_DATE)
from (select distinct INSERT_DATE from TEST_TBL order by INSERT_DATE asc)

The result from above query is used for the in clause for below.

select * from (select log, lot, insert_date from TEST_TBL)
pivot(count(distinct log || insert_date)
for(insert_date) in ('17-JAN-19', '21-JAN-19', '22-JAN-19'))

Now, I want to have this result where the total per columns will be shown at the end of all rows. I tried using GROUP BY ROLLUP but it doesn't work.

enter image description here

I tried this query:

    select * 
    from (select * from (select log, lot, insert_date from TEST_TBL) pivot(count(distinct log || insert_date)
    for(insert_date) in ('17-JAN-19', '21-JAN-19', '22-JAN-19')))
    group by rollup (log);

Can someone help me on what query should I use? Thank you.

Edit: I have used @q4za4 's solution and have arrived at this final query

from   (select *
            from   (select log,
                           lot,
                           insert_date
                    from   TEST_TBL)
            pivot(
                    count(distinct lot || insert_date)
                    for   (insert_date) 
                    in    ('17-JAN-19','21-JAN-19','22-JAN-19'))
    )
    UNION ALL
    select 'TOTAL # OF LOGS',
           sum(jan1719),sum(jan2119),sum(jan2219)
    FROM   (select *
            from   (select log,
                           lot,
                           insert_date
                    from   TEST_TBL)
            pivot(
                    count(distinct lot || insert_date)
                    for   (insert_date) 
                    in    (
                    '17-JAN-19' as jan1719,'21-JAN-19' as jan2119,'22-JAN-19' as jan2219
                    )))

Thank you also to @Ponder Stibbons' solution which also helped me by giving me the idea of making the additional part in my listagg-query to create the first line of the target query.

Solution

  • The fastest approach is to duplicate the same query. Something like:

    select *
from   (select log,
               lot,
               insert_date
        from   TEST_TBL)
pivot(
        count(distinct log || insert_date)
        for   (insert_date) 
        in    ('17-JAN-19', '21-JAN-19', '22-JAN-19'))
)
UNION ALL
select NULL,
       sum(),
       sum(),
       sum(),
FROM   (
        select *
        from   (select log,
                       lot,
                       insert_date
                from   TEST_TBL)
        pivot(
                count(distinct log || insert_date)
                for   (insert_date) 
                in    ('17-JAN-19', '21-JAN-19', '22-JAN-19'))
       )

    between brackets in sum() add cyour column names