I want to see the data as follows
Member Age Category Test1 25 Under 30 Test2 55 51-60 Test3 67 Above 60
I have used the below query (from an answer to my previous question) to calculate the age from DOB and current date and then categorize each age under one name.
This query is creating separate columns, instead I want to see all the categories under one column so that I can group this category column
WITH AgeData as ( SELECT Username, Birthdate, DATEDIFF(YEAR, birthdate, GETDATE()) AS AGE FROM @Table) SELECT Username, Birthdate, Age, CASE WHEN AGE < 30 THEN 1 ELSE 0 END AS 'Under 30', CASE WHEN AGE BETWEEN 31 AND 40 THEN 1 ELSE 0 END AS '31 - 40', CASE WHEN AGE BETWEEN 41 AND 50 THEN 1 ELSE 0 END AS '41 - 50', CASE WHEN AGE > 50 THEN 1 ELSE 0 END AS 'Over 50' FROM AgeData