Oracle add sequential query rows if missing-CodePudding

In Oracle 19c I have a data like:

with t as (
select 1 Cat, 1 id, 11 val from dual
 union all
select 1, 3, 33 from dual
 union all
select 2, 2, 22 from dual
 union all
select 2, 4, 44 from dual)
select * 
  from t

In query result I want to get 4 rows per every cat with ids 1-4 and if there was no such id in that cat a val must be null:

cat	id	val
1	1	11
1	2
1	3	33
1	4
2	1
2	2	22
2	3
2	4	44

CodePudding user response：

Use a PARTITIONed join with a row-generator:

SELECT t.cat,
       i.id,
       t.val
FROM   (SELECT LEVEL AS id FROM DUAL CONNECT BY LEVEL <= 4) i
       LEFT OUTER JOIN t
       PARTITION BY (t.cat)
       ON (i.id = t.id)

Which outputs:

CAT ID VAL

1 1 11

1 2 null

1 3 33

1 4 null

2 1 null

2 2 22

2 3 null

2 4 44

db<>fiddle here