By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with mytab (id, name, c1, c2, c3, ord) as
(
values
(1, 'aaa', 1, null::int, null::int, 10)
, (1, 'aaa', null, 1, null, 20)
, (1, 'aaa', null, null, 1, 30)
, (1, 'aaa', null, null, 2, 40)
, (1, 'aaa', null, 1, null, 50)
, (2, 'bbb', null, null, null, 1)
, (2, 'bbb', 1, null, null, 2)
)
-- Rows enumeration inside all groups
, mytab2 as
(
select row_number () over (partition by id, name
--order by ord
) rn_, t.*
from mytab t
)
--
, a (id, name, rn_, grp, c1, c2, c3) as
(
select id, name, rn_, 1 as grp, c1, c2, c3
from mytab2
where rn_ = 1
union all
select t.id, t.name, t.rn_
, a.grp
+ case
when a.c1 is not null and t.c1 is not null
or a.c2 is not null and t.c2 is not null
or a.c3 is not null and t.c3 is not null
then 1
else 0
end as grp
, case
| ID | NAME | C1 | C2 | C3 |
|---|---|---|---|---|
| 1 | aaa | 1 | 1 | 2 |
| 1 | aaa | null | 1 | 1 |
| 2 | bbb | 1 | null | null |