By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with test_tbl(log, lot, insert_date, other_col) as (
select 1, 'A', date '2019-01-17', 1 from dual union all
select 2, 'B', date '2019-01-17', 5 from dual union all
select 2, 'B', date '2019-01-17', 6 from dual union all
select 3, 'A', date '2019-01-17', 2 from dual union all
select 4, 'A', date '2019-01-21', 1 from dual union all
select 5, 'A', date '2019-01-21', 0 from dual union all
select 6, 'A', date '2019-01-22', 4 from dual union all
select 7, 'B', date '2019-01-22', 5 from dual union all
select 8, 'A', date '2019-01-23', 9 from dual )
select lot, sum(d17) sd17, sum(d22) sd22, sum(d23) sd23
from test_tbl
pivot(count(distinct log) for(insert_date) in (date '2019-01-17' d17,
date '2019-01-22' d22,
date '2019-01-23' d23))
group by rollup(lot)
| LOT | SD17 | SD22 | SD23 |
|---|---|---|---|
| A | 2 | 1 | 1 |
| B | 2 | 1 | 0 |
| null | 4 | 2 | 1 |