How to extract data from this table and convert it into this specific format using t-sql?

I am using SQL Server 2014. Below is the extract from a table called Table1:

ID     Market    Adult   AdultAge   0-5   6-12   13-17
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  

Columns 0-5, 6-12 and 13-17 are age categories and contain the count of children.

I need to convert this Table into the following format and using the following logic:

• If count of Adult is 2, then the 2nd Adult’s age will be the recorded age minus 2.
• Child’s age will be the mid-point of its age category. That is, if there 1 child (or 2 or more children) in a particular category (say, 6-12), then the age of the child (or each child if more than 1), will be (12+6)/2 = 9 years. Same logic applies to the other age categories.

So, here is the final output I am looking for:

ID      Market   Age    Category
100     UK       50      Adult
105     UK       36      Adult
105     UK       34      Adult
107     DE       65      Adult
107     DE        9      Child
110     FR       42      Adult
110     FR       40      Adult
110     FR        9      Child
110     FR        9      Child
110     FR       15      Child
115     BE       18      Adult
115     BE        2.5    Child

I am thinking of using some CASE statements to convert the data but I am really confused as to how to implement same.

Any help would be much appreciated.

Answer

This is a little complicated, but a numbers table would help. You can easily generate one using a recursive CTE (up to 100), or you might have one lying around.

Then you need to unpivot and apply a bit more logic:

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;

Here is a db<>fiddle.