如何为带动态表头的SQL透视表添加列总计行？

最近我碰到一个需求：给带动态表头的透视表添加总计行，试了GROUP BY ROLLUP但没效果，折腾了一阵后结合社区方案解决了，下面是详细过程：

背景与初始查询 #

首先我需要生成透视表的动态日期列表，用这个查询获取：

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

把这个结果代入透视表的IN子句，初始的透视查询是：

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来生成总计行，写了下面的查询但没生效：

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);

最终可行方案 #

后来采用@q4za4的方案，用UNION ALL把原透视结果和汇总行拼接起来，同时给透视后的列指定别名方便求和，最终查询如下：

SELECT log, jan1719, jan2119, 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  
        )
    )
) 
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  
        )
    )
)

额外思路参考 #

另外还要感谢@Ponder Stibbons的方案，他的思路帮我优化了listagg查询，让我能直接生成目标查询的首行内容，让动态构建透视表语句的过程更高效。

内容的提问来源于stack exchange，提问作者C.Ora