By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select v.*
into table1
from (values (100, 'UK', 1, 50, 0, 0, 0),
(105, 'UK', 2, 36, 0, 0, 0),
(107, 'DE', 1, 65, 0, 1, 0),
(110, 'FR', 2, 42, 0, 2, 1),
(115, 'BE', 1, 18, 1, 0, 0)
) v(ID, Market, Adult, AdultAge, [0-5], [6-12], [13-17])
5 rows affected
with numbers as (
select 1 as n
union all
select n + 1
from numbers
where n <= 10
)
select x.*
from table1 t1 cross apply
(select t1.ID, t1.Market, category,
(case when v.category = 'Adult' and n.n = 2 then v.age - 2
else v.age
end) as age
from (values (t1.AdultAge, 'Adult', t1.Adult),
(2.5, 'Child', t1.[0-5]),
(9, 'Child', t1.[6-12]),
(15, 'Child', t1.[13-17])
) v(age, category, cnt) join
numbers n
on n.n <= v.cnt
) x;
| ID | Market | category | age |
|---|---|---|---|
| 100 | UK | Adult | 50.0 |
| 105 | UK | Adult | 36.0 |
| 105 | UK | Adult | 34.0 |
| 107 | DE | Adult | 65.0 |
| 107 | DE | Child | 9.0 |
| 110 | FR | Adult | 42.0 |
| 110 | FR | Child | 9.0 |
| 110 | FR | Child | 15.0 |
| 110 | FR | Adult | 40.0 |
| 110 | FR | Child | 9.0 |
| 115 | BE | Adult | 18.0 |
| 115 | BE | Child | 2.5 |