I am using SQL Server 2014
. Below is the extract from a table called Table1
:
x
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.
Advertisement
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.