hey all,

i have a small table that looks like the following:

gender/age
-------------
male     18
female  32
male     40
female  25
female  26
female  30

how can i store or display horizontally like the following:
the count for each gender in respective age categories

            ageCategory1     ageCategory2     ageCategory3   
male             1                        0                      1
female          2                        2                       0

Legend:
20-29 Category1
30-39 Category2
40-49 Category3

Is this even possible?

thanks,
rodchar

rodchar wrote:
Show quoteHide quoteThis is called a "crosstab" query, and there have been numerous examples
posted to this group in the past week or so.

Hello

Try something like this:

SELECT gender,
  SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) AS
ageCategory1,
  SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) AS
ageCategory2,
  SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END) AS
ageCategory3
FROM smalltable
GROUP BY gender

Razvan

is there, by any chance, to get the vertical totals at the same time?

Show quoteHide quote

rodchar wrote:
I guess that you want something like this:
           ageCategory1     ageCategory2     ageCategory3
male             1                   0                 1
female           2                   2                 0
total            3                   2                 1

This would be much easier done in your report designer (in the
front-end).
However if you insist to do it in the back-end, you can do something
like this:

SELECT gender,
  SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) AS
ageCategory1,
  SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) AS
ageCategory2,
  SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END) AS
ageCategory3
FROM smalltable
GROUP BY gender
UNION ALL
SELECT 'total' as gender,
  SUM(CASE WHEN age BETWEEN 20 AND 29 THEN 1 ELSE 0 END) AS
ageCategory1,
  SUM(CASE WHEN age BETWEEN 30 AND 39 THEN 1 ELSE 0 END) AS
ageCategory2,
  SUM(CASE WHEN age BETWEEN 40 AND 49 THEN 1 ELSE 0 END) AS
ageCategory3
FROM smalltable

Razvan

Here is a couple of resources for that tast.

Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955


How to rotate a table in SQL
Serverhttp://support.microsoft.com/default.aspx?scid=kb;en-us;175574




Show quoteHide quote

thanx everyone for the help.

Show quoteHide quote

master table and child table andfield concatenation and loop records ?
Question for T-SQL Gurus
VIEW's ORDER BY columns necessary in column list
FOREIGN KEY that REFERENCES a server login for a database?
How to get the record created date?