Board index » delphi » How to create crosstab query?

How to create crosstab query?

How to create crosstab query?
 

Re:How to create crosstab query?


Hi, Gizmos.

What you understand under crosstab query? What you want exactly to make?

Regard.

ps. I'm sorry for my very bad English.
--
Petio Tonev
pto...@iname.com
www.i.am/ptonev , www.w3.to/tonevsoft

Re:How to create crosstab query?


Quote
On Tue, 20 Jul 1999 08:55:26 +0800, "Gizmos" <a...@iist.unu.edu> wrote:
>How to create crosstab query?

How can I query a list of people (for example) with the following
columns:

   Customer, Zone, State

and turn it into

ZONE       ALABAMA     FLORIDA     MAINE

10                   200              432              86
20                   111                26             388

That's a general crosstab with an aggregate function (and is the basis
for just about EVERY type of crosstab I have ever seen...)

I'm not familiar with this in SQL and need to write several of this
type of query.

Can anyone help?

Bill

b...@droste1.com

Re:How to create crosstab query?


Quote

>ZONE       ALABAMA     FLORIDA     MAINE

>10                   200              432              86
>20                   111                26             388

>That's a general crosstab with an aggregate function (and is the basis
>for just about EVERY type of crosstab I have ever seen...)

>I'm not familiar with this in SQL and need to write several of this
>type of query.

There are a few databases (like MS Access) that support crosstab queries with a
proprietary syntax but there is no universal syntax and not even universal
support for this feature.
Local SQL does not support Crosstab queries.

You can do it with a series of tables.
with local tables you would do it by first doing two queries.  One to give you
all zones and one to give you all States.

Then user dbimakePerminat to turn the first of these into a permanent table

next iterate through the records of States and perform an outerJoin query to the
saved table.  After each query use dbiMakePerminat to save the query and go on
to the next state.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:How to create crosstab query?


Quote
Bill Artemik wrote:
> On Tue, 20 Jul 1999 08:55:26 +0800, "Gizmos" <a...@iist.unu.edu> wrote:

> >How to create crosstab query?

> How can I query a list of people (for example) with the following
> columns:

>    Customer, Zone, State

> and turn it into

> ZONE       ALABAMA     FLORIDA     MAINE

> 10                   200              432              86
> 20                   111                26             388

> That's a general crosstab with an aggregate function (and is the basis
> for just about EVERY type of crosstab I have ever seen...)

> I'm not familiar with this in SQL and need to write several of this
> type of query.

> Can anyone help?

> Bill

> b...@droste1.com

    Try

     Select State1.Customers as Zone, State2.Customers as Alabama,
State3.Customers as Maine
           From TblState
      Left Outer Join TblState State1 on State1.Name = "ZONE"
      Left Outer Join TblState State2 on State2.Name = "ALABAMA"
      Left Outer Join TblState State3 on State3.Name = "MAINE"

    ZONE       ALABAMA     FLORIDA     MAINE

    10                   200              432              86
    20                   111                26             388

--
  Regards.

  Marcos Ortega
 UIN  : 32799293
"May The Source Be With You !"

Other Threads