I have a table containing numbers of people in each area, by age. There is a column for each age, as shown in this table (junk data):

I need to group these figures into age bands (0-19. 20-39, 40-59...) eg:

What is the best way to do this?

I could do a simple SUM as below, but that feels like a massive amount of script for something that feels like it should be straightforward.

SELECT 
[0] [1] [2] ...[19] AS [0-19],
[20] [21] [22] ...[39] AS [20-39]
...

Is there a simpler way? I'm wondering if PIVOT can help but am struggling to visualise how to use it to get my desired result. Hoping I'm missing something obvious!

EDIT This is how the data has been supplied to me, I know it's not a great table design but unfortunately that's out of my hands.

CodePudding user response：

I would suggest creating a view on top of your table like so:

CREATE VIEW v_t_normal
SELECT Area, Age, Value
FROM t
CROSS APPLY (VALUES
    (0,  [0])
    (1,  [1])
    ...
    (90, [90 ])
) AS ca(Age, Value)

That view will normalize present your data in somewhat normalized form. You will not be able to edit the data in the view but you should be able to perform basic math and aggregation on the data. The 90 value will still cause headache as it encapsulates more than one value.

CodePudding user response：

I'm going to answer by suggesting an alternative table design which will make life easier:

Area | Age | Count
A    | 0   | 123
A    | 1   | 65
...
B    | 0   | 442

Here we are storing each area's age in a separate record, rather than column. With this design in place, your ask is easy to come by using conditional aggregation:

SELECT
    Area,
    SUM(CASE WHEN Age BETWEEN 0 AND 19  THEN Count ELSE 0 END) AS [0-19],
    SUM(CASE WHEN Age BETWEEN 20 AND 39 THEN Count ELSE 0 END) AS [20-39],
    ...
FROM yourNewTable
GROUP BY Area;