Adding rows with null measures in ORACLE SQL by select statment

user_13799501 0
Viewed 71

I would like to ask for a little help.

I have the following data

Name   Date           Count
P      01/01/20       4
P      01/03/20       3
P      01/05/20       2
P      01/06/20       5
T      01/12/19       4
T      01/01/20       1

I would like to add the rows with nulls in the count field, to have the following results

Name   Date           Count
P      01/12/19       null
P      01/01/20       4
P      01/02/20       null
P      01/03/20       3
P      01/04/20       null
P      01/05/20       2
P      01/06/20       5
T      01/12/19       4
T      01/01/20       1
T      01/02/20       null
T      01/03/20       null
T      01/04/20       null
T      01/05/20       null
T      01/06/20       null

Could you please help me?

recursive-query select date oracle sql
4 Answers

One option uses a recursive query to generate the dates. You can then cross join it with the distinct names available in the table, and finally bring the table with a left join:

with cte (dt, max_dt) as (
    select min(dt), max(dt) from mytable
    union all
    select dt + interval '1' month, max_dt from cte where dt < max_dt
)
select c.dt, n.name, t.cnt
from cte c
cross join (select distinct name from mytable) n
left join mytable t on t.name = n.name and t.dt = c.dt
order by n.name, c.dt

Note that date and count are not valid identifiers in Oracle - I renamed them to dt and cnt in the query.

Demo on DB Fiddle:

DT        | NAME |  CNT
:-------- | :--- | ---:
01-DEC-19 | P    | null
01-JAN-20 | P    |    4
01-FEB-20 | P    | null
01-MAR-20 | P    |    3
01-APR-20 | P    | null
01-MAY-20 | P    |    2
01-JUN-20 | P    |    5
01-DEC-19 | T    |    4
01-JAN-20 | T    |    1
01-FEB-20 | T    | null
01-MAR-20 | T    | null
01-APR-20 | T    | null
01-MAY-20 | T    | null
01-JUN-20 | T    | null
user_106767160

Find min and max month, create calendar using this values, join this calendar with your table:

dbfiddle

with 
  dates as (select min(date_) mnd, max(date_) mxd from tbl),
  cal as (select add_months(mnd, level - 1) mth
           from dates connect by level - 1 <= months_between(mxd, mnd))
select * from cal left join tbl partition by (name) on (date_ = mth)
user_44449800

You can use the row generator technique as follows:

WITH DATES(DT) AS
(SELECT ADD_MONTHS(TRUNC(SYSDATE,'MON'),LEVEL-1) FROM DUAL CONNECT BY LEVEL <= 7) -- use logic as per your requirement
SELECT NM.NAME, D.DT, T.COUNT 
FROM DATES D 
JOIN (SELECT DISTINCT NAME FROM YOUR_TABLE) NM ON 1=1 --CROSS JOIN FOR CREATING 7 RECORDS FOR EACH NAMES
LEFT JOIN YOUR_TABLE T ON D.DT = T.DATE AND NM.NAME = T.NAME
user_115656290 
insert into table_name values ("P","01/12/19","");
insert into table_name values ("P","01/02/20","");
insert into table_name values ("P","01/04/20","");
insert into table_name values ("T","01/02/20","");
insert into table_name values ("T","01/03/20","");
insert into table_name values ("T","01/04/20","");
insert into table_name values ("T","01/05/20","");
insert into table_name values ("T","01/06/20","");

and then query this: select * from table_name order by Name asc, Date asc;

user_94139100
Related