Skip to content
Advertisement

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:

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:

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:

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement