Re: Creating A Crosstab Query

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 1997/12/05
Message-ID: <6686h1$oed$1@hermes.is.co.za>#1/1

Steve McDaniels wrote in message <666s3b$as8_at_yobi.sierra.com>...
>For those of you who haven't used MS-Access, a cross-tab query takes
>arbitrary data from fields in ROWS and "pivots" them into column headings.

Pivots can be done using standard SQL statements. It may be a wee bit complex, but it works. For example, let's say you have the following table: QRESULTS=( Year, Quarter, Turnover )

YEAR QUARTER TURNOVER
---- ------- --------

1996 1       100
1996 2       150
1996 3       200
1996 4       150
1997 1       200
1997 2       230
1997 3       220
1997 4       290

And you want to display the output as follows: YEAR Q1 TURNOVER Q2 TURNOVER Q3 TURNOVER Q4 TURNOVER ----- ----------- ----------- ----------- -----------

1996  100          150          200          150
1997  200          230          220          290

First thing to do is to create a pivot table to pivot the result. The pivot table construct is as follows. A "pivot column" to contain the values of the column to pivot on, and one "value column" per pivot value. Ok, we want to pivot by quarter and obtain 4 values per pivot.

PivotTable=(Quarter, Q1, Q2, Q3, Q4)

Now we create a bitmask in this table that defines the pivot "rules". A value of 1 indicates that the "value column" must be pivoted for the specified "pivot column". A zero means that the "value column" is not applicable to that "pivot column".

Quarter Q1 Q2 Q3 Q4
------- -- -- -- --

1       1  0  0  0
2       0  1  0  0
3       0  0  1  0
4       0  0  0  1

And now do the pivot using standard SQL:

SELECT
  q.year,

  SUM(q.turnover*p.q1) "Quarter 1",
  SUM(q.turnover*p.q2) "Quarter 2",
  SUM(q.turnover*p.q3) "Quarter 3",
  SUM(q.turnover*p.q4) "Quarter 4"

FROM qresults q, pivottable p
WHERE q.quarter = p.quarter
GROUP BY q.year
ORDER BY q.year

As you can see, in the "QUARTER 1" column in the above select statement, quarter 1 from the results table will be multiplied with 1 for the resulting sum of 100. The sums for quarter 2,3 and 4 values will all be zero as they are multiplied with zero (e.g. the pivot rule for quarter 2 says that Q1 is zero - see row 2 in the pivot table).

And that's how a crosstab/pivot query can be done! :-) I'm not sure who was the person who first though of this fairly simple and very elegant technique, but hats off to him.

regards,
Billy Received on Fri Dec 05 1997 - 00:00:00 CST