Re: transposing data for a view

Jeff,

> SELECT
>    scanid,
>    sum ( CASE WHEN region = 'A' THEN volume ELSE NULL
> )
>      AS A_volume,
>    sum ( CASE WHEN region = 'B' THEN volume ELSE NULL
> )
>      AS B_volume,
>    sum ( CASE WHEN region = 'C' THEN volume ELSE NULL
> )
>      AS C_volume
> FROM table
> GROUP BY scanid;

Hey, that's an elegant solution to doing it in 7.0.3.  I hadn't thought
of it.  Jeremy, never mind what I said about being forced to upgrade.
Upgrading *would* still be a good idea, of course.

Of couse, it's only *half* a solution.  Your query will result in:

scanid    A_volume  B_volume  C_volume
1    34.5
1          55.1
1                12.3
2    11.1
etc.

For the second half of the solution, Jeremy needs to create the above as
a view ('volume_rollup_1') and apply this second view:

SELECT scanid, SUM(A_volume) as A_volume, SUM(B_Volume) as B_Volume,
SUM(C_volume) as C_volume
FROM volume_rollup_1;

This will give Jeremy the "pivot" grid he's looking for.

> BTW, I don't believe the self-join approach proposed
> earlier will work, because joining on "scanid" will
> create a cartesian type join where the region values
> will be duplicated (multiplicated!).

Not if you're talking about my query, they won't.  I use that query form
in many projects to create roll-ups; it's the "best" SQL92 approach to
the "pivot table" problem.  However, it will not work in 7.0.3.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco