This is like a "Crosstab" Do some searching on crosstab and or dynamic cross tab for more examples.DECLARE @MyTable TABLE(FirstName VARCHAR(50), Age TINYINT)INSERT INTO @MyTable(FirstName, Age) VALUES('A0', 29)INSERT INTO @MyTable(FirstName, Age) VALUES('A1', 11)INSERT INTO @MyTable(FirstName, Age) VALUES('A2', 22)INSERT INTO @MyTable(FirstName, Age) VALUES('A3', 30)INSERT INTO @MyTable(FirstName, Age) VALUES('B0', 32)INSERT INTO @MyTable(FirstName, Age) VALUES('B1', 35)INSERT INTO @MyTable(FirstName, Age) VALUES('C0', 36)INSERT INTO @MyTable(FirstName, Age) VALUES('C1', 37)INSERT INTO @MyTable(FirstName, Age) VALUES('C2', 38)INSERT INTO @MyTable(FirstName, Age) VALUES('C3', 39)INSERT INTO @MyTable(FirstName, Age) VALUES('C4', 36)INSERT INTO @MyTable(FirstName, Age) VALUES('C5', 37)INSERT INTO @MyTable(FirstName, Age) VALUES('C6', 38)INSERT INTO @MyTable(FirstName, Age) VALUES('D0', 69)INSERT INTO @MyTable(FirstName, Age) VALUES('D1', 101)INSERT INTO @MyTable(FirstName, Age) VALUES('D2', 40)INSERT INTO @MyTable(FirstName, Age) VALUES('D3', 55)select SUM(case WHEN Age <=30 THEN 1 ELSE 0 END) AS '30_or_Less',SUM(case WHEN Age BETWEEN 31 AND 35 THEN 1 ELSE 0 END) AS '31_to_35',SUM(case WHEN Age BETWEEN 36 AND 39 THEN 1 ELSE 0 END) AS '36_to_39',SUM(case WHEN Age >=40 THEN 1 ELSE 0 END) AS '40_or_Greater'FROM @MyTableMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> 
SQL age banding
Posted - 2004-12-29 : 18:47:55
