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 with a query

Hi,
I'm new to fulltext search and am having a problem formulating a query.
I have a table with key, title, description within it. I'm trying to do
a search, but weight results in the title higher than the description.
I've only written this so far.
select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
rank desc
Any help on doing a similar query, but ranking results in title higher
appreciated.
Spondishy,
Could you post back with the full output of the following SQL code as this
is helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Additionally, have you tried using the WEIGHT parameter on the title column?
Review the SQL Server BOL for either CONTAINSTABLE or FREETEXTTABLE for more
info on Weight.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Spondishy" <spondishy@.tiscali.co.uk> wrote in message
news:1131363042.622970.188750@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm new to fulltext search and am having a problem formulating a query.
> I have a table with key, title, description within it. I'm trying to do
> a search, but weight results in the title higher than the description.
> I've only written this so far.
> select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
> title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
> rank desc
> Any help on doing a similar query, but ranking results in title higher
> appreciated.
>
|||I'd try something like this
CREATE TABLE HTML
(pk int not null identity CONSTRAINT htmlPK PRIMARY KEY,
TITLE Varchar(20),
DESCRIPTION Varchar(20)
)
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('this is a test','this is a
test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('nada','test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','nada')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test nada','nada test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test test','test test')
INSERT INTO HTML(TITLE, DESCRIPTION) VALUES('test','test test test test')
exec sp_fulltext_catalog N'test', N'create'
GO
exec sp_fulltext_table N'[dbo].[HTML]', N'create', N'test', N'htmlPK'
GO
exec sp_fulltext_column N'[dbo].[HTML]', N'TITLE', N'add', 1033
GO
exec sp_fulltext_column N'[dbo].[HTML]', N'DESCRIPTION', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[HTML]', N'activate'
GO
sp_fulltext_catalog 'test','start_Full'
SELECT pk, TITLE, DESCRIPTION, TITLE.RANK, DESCRIPTION.RANK FROM HTML
JOIN (SELECT * FROM CONTAINSTABLE(HTML, DESCRIPTION,'test')) AS DESCRIPTION
ON DESCRIPTION.[KEY]=HTML.PK
JOIN (SELECT * FROM CONTAINSTABLE(HTML, TITLE,'test')) AS TITLE ON
TITLE.[KEY]=HTML.PK
ORDER BY TITLE.[RANK] DESC, DESCRIPTION.RANK DESC
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spondishy" <spondishy@.tiscali.co.uk> wrote in message
news:1131363042.622970.188750@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm new to fulltext search and am having a problem formulating a query.
> I have a table with key, title, description within it. I'm trying to do
> a search, but weight results in the title higher than the description.
> I've only written this so far.
> select [KEY], Rank, listing_id, title FROM FREETEXTTABLE (listing,
> title, 'my phrase') F JOIN listing p on p.listing_id=F.[KEY] order by
> rank desc
> Any help on doing a similar query, but ranking results in title higher
> appreciated.
>