Actually, there's an even better way to do crosstabs with SELECT. Suppose
for example, you have the following table Charges:
State SaleType Amount
MA CHRG 100
MA CHRG 200
MA CHK 400
IL CHK 300
IL CHK 200
KY CHRG 100
We want a crosstab which shows:
State CHK CHRG Total
IL 500 0 500
KY 0 100 100
MA 400 300 700
--------------------------------
Total 900 400 1300
Here's the SELECT:
SELECT State,+
SUM(IFEQ(SaleType,'CHK',Amount,0)),
SUM(IFEQ(SaleType,'CHRG',Amount,0)) +
FROM Charges
Love those logical functions!
David Blocker
Total
----- Original Message -----
From: "Jim Blackburn" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 07, 2001 12:40 PM
Subject: Re: Saving the crosstab results as a table or view
> rich wrote:
> >
> > Is there a way to save the results of a crosstab as as table or view?
> >
> > rich
> The only way is to output to a file then load that file to a table.
>
> But, you can get the same information, organized differently:
> cross sum ClmA for ClmB by ClmC FROM Table
>
> vs:
> select ClmB, ClmC, sum(ClmA) from Table GROUP BY ClmB, ClmC
>
> the latter provides the same information, organized in a way that it will
go directly into a table if you wish, or can be set up as a view. Since the
latter form follows the relational model, it is easier to use for further
processing.
>
> Jim Blackburn
> Kodiak
