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

help required in writing a query

hi all,

consider the following scenario:

I have 2 tables ModelSalesUnitCode and SalesUnitCode with following data:

ModelSalesUnit
Kis Type Salu_cd
PC 6 2AA01
WA 3 2AA01
Wa 3 3AA01

SalesUnitCode
Salu_cd Kis Type Salu_desc
2AA01 null null generic description
2AA01 WA 3 WA description
3AA01 WA 3 generic description

Now I write this query to get the salu_cd and their respective descriptions for the model with (kis = WA and Type = 3)

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'

This gives me
kis type salu_cd salu_desc
WA 3 2AA01 generic description
WA 3 2AA01 WA description
WA 3 3AA01 generic description

is it possible to modify the above query in such a way that it returns

kis type salu_cd salu_desc
WA 3 2AA01 WA description
WA 3 3AA01 generic description

meaning where description exists for (Kis = WA and Type = 3) pick it up (WA Description in above case) if it does not exist then pick the generic description.

Thanx in advance
Omer

/Add clause in WHERE:

Select
a.kis,
a.type,
a.salu_cd,
b.salu_desc
From
modelsalesunitcode a, salesunitcode b
Where
b.salu_cd = a.salu_cd
And
a.kis = 'WA'
And
a.type = '3'
--------
AND b.kis IS NOT NULL
--------