Possible combinations of records in sql server based on condition

Question

My Input is like below:

c1    c2      req         qty
1      A1     234         34
1      A1     547         45
1      A1     12P7        0.25
1      A1     12P8        0.25
1      A1     12P9        0.25

My Output should be like below:(Need a combinations of records with 'P' as values for every c1,c2 )

c1    c2      c3    req         qty
1      A1      1    234         34
1      A1      1    547         45
1      A1      1    12P7        0.75

1      A1      2    234         34
1      A1      2    547         45
1      A1      2    12P8        0.75

1      A1      3    234         34
1      A1      3    547         45
1      A1      3    12P7        0.375
1      A1      3    12P8        0.375

1      A1      4    234         34
1      A1      4    547         45
1      A1      4    12P7        0.375
1      A1      4    12P9        0.375

1      A1      5    234         34
1      A1      5    547         45
1      A1      5    12P8        0.375
1      A1      5    12P9        0.375

Answer 1

The basic idea is to enumerate the difference conditions. One trick is allocating req. This is handled by calculating the total and dividing by the number of rows in the group:

select t.c1, t.c2, v.c3, t.req,
       (case when t.req like '12%'
             then (qty_total /
                   sum(case when t.req like '12%' then 1 else 0 end) over (partition by v.c3)
                  )
             else qty
        end)
from (select t.*, sum(case when req like '12%' then qty end) over () as qty_total
      from t 
     ) t cross join
     (values (1), (2), (3), (4), (5)) v(c3)
where t.req not like '12%' or
      (v.c3 = 1 and t.req in ('12P7') or
       v.c3 = 2 and t.req in ('12P8') or
       v.c3 = 3 and t.req in ('12P7', '12P8') or
       v.c3 = 4 and t.req in ('12P7', '12P9') or
       v.c3 = 5 and t.req in ('12P8', '12P9') 
      )
order by c3, c1, c2;

If your database doesn't support this exact syntax, it supports something similar.

Here is a db<>fiddle.