Help with complicated SQL query

Hi everyone.

This is my scenario:

I have two tables:
persons (id,age,roleid)
roles (roleid,description)

I want to build a sql query to produce the following rows (example):
range(age) role1 role2 role3 ... rolen
0 to 4 11 24 5 7
5 to 9 42 7 1 0
10 to 14 14 21 9 8
15 to 20 7 0 7 19

I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.

Thanks for all your help!

Rolandyou want a cross tab query using the CASE statement. Read about both in books online and come back if you are still having trouble.|||Lookup "Crosstab" in Books Online.|||this isn't quite as straightforward as it first appearsselect range
, sum(case when roleid =1
then rows else 0 end) as role1
, sum(case when roleid =2
then rows else 0 end) as role2
, ...
, sum(case when roleid =n
then rows else 0 end) as rolen
from (
select '0 to 4' as range
, roleid
, count(*) as rows
from persons
where age between 0 and 4
group by roleid
union all
select '5 to 9' as range
, roleid
, count(*) as rows
from persons
where age between 5 and 9
group by roleid
union all
select '10 to 14' as range
, roleid
, count(*) as rows
from persons
where age between 10 and 14
group by roleid
union all
select '15 to 20' as range
, roleid
, count(*) as rows
from persons
where age between 15 and 20
group by roleid
) as dt
group by range|||Great r937!! thanks!! everything worked perfectly!

Roland