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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco