Re: Cross tabulations in SQL?

From: D Guntermann <guntermann_at_hotmail.com>
Date: Tue, 12 Aug 2003 02:08:53 GMT
Message-ID: <HJHHys.FF4_at_news.boeing.com>

Hi,

The following might be something close to what you are looking for. The solution is limited in that it is constrained to propietary features of 8i - e.g. the DECODE function. The decode function is deprecated in favor of the SQL99 standard CASE function, for Oracle 9i at least. Hopefully some analogous feature is available to you also.

Also, note that I greatly simplified the solution to only include the most essential elements in your example. Assume also that a person's name is sufficient as a unique identity for purposes of exposition.

 CREATE TABLE membership
 (

    name CHAR(8)

      CONSTRAINT pk_name_mbrship
        PRIMARY KEY,
    gender CHAR(1)
      CONSTRAINT ck_validgender_mbrship
        CHECK (gender in ('M', 'F', 'U'))
      CONSTRAINT nn_gender_mbrship
        NOT NULL,
    county  VARCHAR2(15)
      CONSTRAINT ck_validcnty_mbrship
        CHECK (county IN ('DUBLIN', 'CORK', 'MAYO'))
      CONSTRAINT nn_county_mbrship
        NOT NULL

 );

Table created.

SQL> INSERT INTO membership
  2 VALUES ('MIKE', 'M', 'DUBLIN'); 1 row created.

SQL> INSERT INTO membership
  2 VALUES ('JUDY', 'F', 'CORK'); 1 row created.

SQL> INSERT INTO membership
  2 VALUES ('JAX', 'U', 'MAYO'); 1 row created.

SQL> INSERT INTO membership
  2 VALUES ('MICHELLE', 'F', 'MAYO'); 1 row created.

SQL> SELECT name, gender, county
  2 FROM membership
  3 /

NAME G COUNTY
-------- - ---------------

MIKE     M DUBLIN
JUDY     F CORK
JAX      U MAYO

MICHELLE F MAYO   1 SELECT county, COUNT(DECODE(gender, 'M', name)) Males,

  2         COUNT(DECODE(gender, 'F', name)) Females,
  3         COUNT(DECODE(gender, 'U', name)) Uknown

  4 FROM membership
  5* GROUP BY county
SQL> / COUNTY MALES FEMALES UKNOWN --------------- --------- --------- ---------

CORK                    0         1         0
DUBLIN                  1         0         0
MAYO                    0         1         1

Regards,

Dan Guntermann

"Paul" <paul_at_not.a.chance.ie> wrote in message news:MPG.19a1e26c3f31208098970a_at_news1.eircom.net...
>
>
> Hi all,
>
>
> I have a table for a membership system with many fields, but the
> relevant ones here are Gender (M, F, U (U is from a legacy system)),
> Category (Life, Senior, Junior & Leader) and County (26 values here,
> I'll use 3 as an example - Dublin, Cork and Mayo).
>
> What I want is something like
>
> Gender by County
>
>
> | M | F | U |
> -------------------------------
> Dublin | 12 | 14 | 5 | 31
> Cork | 5 | 7 | 2 | 14
> Mayo | 6 | 6 | 3 | 15
> ---------------------------|----
> | 23 | 27 | 10 | 50
>
>
> What is the closest that I can come to doing this in SQL?
>
>
> I have asked about this on a newsgroup that I frequent that deals with
> my db server of choice (Interbase Open Source) and have been told that I
> can do it in a Stored Proceedure, but even then I have to know in
> advance the number of entris in the Gender "table" for example.
>
>
> If it's not possible at the moment, is it not worth considering as
> functionality that should be part of a db server's capabilities?
>
> Do any servers do it at the moment?
>
>
> Any ideas, debates, rants, URLs, references welcome!
>
>
>
> Paul...
>
>
>
>
> --
>
> plinehan__AT__yahoo__DOT__com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04
>
> p.s. just lacerated a tendon in left hand, so pls
> excuse typos and tricky abbrevs - TIA.
Received on Tue Aug 12 2003 - 04:08:53 CEST