>> I tried this with MS Access using Transform and PIVOT that was
generated by the Cross Tab wizard, but I can't use that code in the SQL
Query Tool. <<
let me give you more answer than you really wanted <g> by quoting from
my book, SQL FOR SMARTIES:
23.6 Cross Tabulations
A cross tabulation, or crosstab for short, is a common statistical
report. It can be done in IBM's QMF tool, using the ACROSS summary
option, and in many other SQL-based reporting packages. SPSS, SAS, and
other statistical packages have library procedures or language
constructs for crosstabs. Many spreadsheets can load the results of SQL
queries and perform a crosstab within the spreadsheet.
If you can use a reporting package on the server in a client/server
system instead of the following method, do so. It will run faster and
in less space than the method discussed here. However, if you have to
use the reporting package on the client side, the extra time required
to transfer data will make these methods on the server side much
faster.
A one-way crosstab "flattens out" a table to display it in a report
format. Assume that we have a table of sales by product and the dates
the sales were made. We want to print out a report of the sales of
products by years for a full decade. The solution is to create a table
and populate it to look like an identity matrix (all elements on the
diagonal are 1, all others zero), then join the Sales table to it.
CREATE TABLE Sales
(product CHAR(15) NOT NULL,
price DECIMAL(5,2) NOT NULL,
qty INTEGER NOT NULL,
salesyear INTEGER NOT NULL);
CREATE TABLE CrossTab
(year INTEGER NOT NULL,
year1 INTEGER NOT NULL,
year2 INTEGER NOT NULL,
year3 INTEGER NOT NULL,
year4 INTEGER NOT NULL,
year5 INTEGER NOT NULL,
year6 INTEGER NOT NULL,
year7 INTEGER NOT NULL,
year8 INTEGER NOT NULL,
year9 INTEGER NOT NULL,
year10 INTEGER NOT NULL);
The table would be populated as follows:
year year1 year2 year3 year4 year5 ... year10
============================================================
1990 1 0 0 0 0 ... 0
1991 0 1 0 0 0 ... 0
1992 0 0 1 0 0 ... 0
1993 0 0 0 1 0 ... 0
1994 0 0 0 0 1 ... 0
1999 0 0 0 0 0 ... 1
The query to produce the report table is
SELECT S1.product,
SUM(S1.qty * price * C1.year1),
SUM(S1.qty * price * C1.year2),
SUM(S1.qty * price * C1.year3),
SUM(S1.qty * price * C1.year4),
SUM(S1.qty * price * C1.year5),
...
SUM(S1.qty * price * C1.year10)
FROM Sales AS S1, CrossTab AS C1
WHERE S1.year = C1.year
GROUP BY S1.product;
Obviously, (price * qty) is the total dollar amount of each product in
each year. The yearN column will be either a 1 or a zero. If it is a
zero, the total dollar amount in the SUM() is zero; if it is a 1, the
total dollar amount in the SUM() is unchanged.
This solution lets you adjust the time frame being shown in the report
by replacing the values in the year column to whatever consecutive
years you wish.A two-way crosstab takes two variables and produces a
spreadsheet with all values of one variable on the rows and all values
of the other represented by the columns. Each cell in the table holds
the COUNT of entities that have those values for the two variables.
NULLs will not fit into a crosstab very well, unless you decide to make
them a group of their own or to remove them.
There are also totals for each column and each row and a grand total.
Crosstabs of (n) variables are defined by building an n-dimensional
spreadsheet. But you cannot easily print (n) dimensions on two-
dimensional paper. The usual trick is to display the results as a two-
dimensional grid with one or both axes as a tree structure. The way the
values are nested on the axis is usually under program control;
thus, "race within sex" shows sex broken down by race, whereas "sex
within race" shows race broken down by sex.
Assume that we have a table, Personnel (empno, sex, race, jobno,
salary), keyed on employee number, with no NULLs in any columns. We
wish to write a crosstab of employees by sex and race, which would look
like this:
Asian Black Caucasian Hispanic Other TOTALS
=============================================================
Male 3 2 12 5 5 27
Female 1 10 20 2 9 42
=========================================================
TOTAL 4 12 32 7 14 69
The first thought is to use a GROUP BY and write a simple query, thus:
SELECT sex, race, COUNT(*)
FROM Personnel
GROUP BY sex, race;
This approach works fine for two variables and would produce a table
that could be sent to a report writer program to give a final version.
But where are your column and row totals? This means you also need to
write these two queries:
SELECT race, COUNT(*) FROM Personnel GROUP BY race;
SELECT sex, COUNT(*) FROM Personnel GROUP BY sex;
However, what I wanted was a table with a row for males and a row for
females, with columns for each of the racial groups, just as I drew it.
But let us assume that we want to get this information broken down
within a third variable, say job code. I want to see the jobno and the
total by sex and race within each job code. Our query set starts to get
bigger and bigger. A crosstab can also include other summary data, such
as total or average salary within each cell of the table.
23.6.1 Crosstabs by Cross Join
A solution proposed by John M. Baird of Datapoint, in San Antonio,
Texas, involves creating a matrix table for each variable in the
crosstab, thus:
SexMatrix
sex Male Female
==================
'M' 1 0
'F' 0 1
RaceMatrix
race Asian Black Caucasian Hispanic Other
========================================================
Asian 1 0 0 0 0
Black 0 1 0 0 0
Caucasian 0 0 1 0 0
Hispanic 0 0 0 1 0
Other 0 0 0 0 1
The query then constructs the cells by using a CROSS JOIN (Cartesian
product) and summation for each one, thus:
SELECT jobno,
SUM(asian * male) AS AsianMale,
SUM(asian * female) AS AsianFemale,
SUM(black * male) AS BlackMale,
SUM(black * female) AS BlackFemale,
SUM(cauc * male) AS CaucMale,
SUM(cauc * female) AS CaucFemale,
SUM(hisp * male) AS HispMale,
SUM(hisp * female) AS HispFemale,
SUM(other * male) AS OtherMale,
SUM(other * female) AS OtherFemale,
FROM Personnel, SexMatrix, RaceMatrix
WHERE (RaceMatrix.race = Personnel.race)
AND (SexMatrix.sex = Personnel.sex)
GROUP BY jobno;
Numeric summary data can obtained from this table. For example, the
total salary for each cell can be computed by SUM(<race> * <sex> *
salary) AS <cell name> in place of what we have here.
23.6.2 Crosstabs by Outer Joins
Another method, due to Jim Panttaja, uses a series of temporary tables
or VIEWs and then combines them with OUTER JOINs.
CREATE VIEW Guys (race, maletally)
AS SELECT race, COUNT(*)
FROM Personnel
WHERE sex = 'M'
GROUP BY race;
Correspondingly, you could have written:
CREATE VIEW Dolls (race, femaletally)
AS SELECT race, COUNT(*)
FROM Personnel
WHERE sex = 'F'
GROUP BY race;
But they can be combined for a crosstab, without column and row totals,
like this:
SELECT Guys.race, maletally, femaletally
FROM Guys LEFT OUTER JOIN Personnel
ON Guys.race = Pesonnel.race
WHERE Personnel.sex = 'F'
GROUP BY Guys.race, maletally;
The idea is to build a starting column in the crosstab, then
progressively add columns to it. You use the LEFT OUTER JOIN to avoid
missing-data problems.
23.6.3 Crosstabs by Subquery
Another method takes advantage of the orthogonality of correlated
subqueries in SQL-92. Think about what each row or column in the
crosstab wants.
SELECT race,
(SELECT COUNT(*)
FROM Personnel AS P1
WHERE P0.race = P1.race
AND sex = 'M') AS MaleTally,
(SELECT COUNT(*)
FROM Personnel AS P2
WHERE P0.race = P2.race
AND sex = 'F') AS FemaleTally
FROM Personnel AS P0;
An advantage of this approach is that you can attach another column to
get the row tally by adding
(SELECT COUNT(*)
FROM Personnel AS P3
WHERE P0.race = P3.race) AS RaceTally
Likewise, to get the column tallies, union the previous query with
SELECT' Summary',
(SELECT COUNT(*)
FROM Personnel
WHERE sex = 'M') AS GrandMaleTally,
(SELECT COUNT(*)
FROM Personnel
WHERE sex = 'F') AS GrandFemaleTally,
(SELECT COUNT(*)
FROM Personnel) AS GrandTally
FROM Personnel;
--CELKO--
Sent via Deja.com http://www.deja.com/
Before you buy.
by