Trying to build a Cross Tab Query

Trying to build a Cross Tab Query

Post by Peter DeSimon » Fri, 04 Feb 2000 04:00:00

I have two fields I want to work from in a table.

Field 1 has 8 possible values, and I would like those to be my columns.  The
content of the Cross Tab is a Sum of Field 1 at each position.

Field 2 is my rows.

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.  I am
going to use this SQL Statement with Active Server Pages in the end and talk
to a MS SQL Table.

Any assistance would be greatful.

Peter DeSimone


Trying to build a Cross Tab Query

Post by Bernd Jungblut » Sat, 05 Feb 2000 04:00:00

look at the onlinehelp SQL-Server. under the point 'pivot-table' you find an
good luck


Trying to build a Cross Tab Query

Post by joe_ce.. » Sat, 05 Feb 2000 04:00:00

>> 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

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

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.

(product CHAR(15) NOT NULL,
 price DECIMAL(5,2) NOT NULL,
 salesyear 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:

 sex   Male Female
 'M'     1   0
 'F'     0   1

 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 ( =
 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)
      FROM Personnel
     WHERE sex = 'M'
     GROUP BY race;

Correspondingly, you could have written:

 CREATE VIEW Dolls (race, femaletally)
      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 = '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

   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;


Sent via
Before you buy.


1. Moving Access Cross Tab Query to SQL Cross Tab

I have this access query that I am trying to move into SQL as Cross Tab query.  Every time I move this one into Query Analyzer, it fails.  Any help would be greatly appreciated

I have the visual design if anyone would like to take a stab at it

Or maybe its just a complex Summary Query

SELECT DutySquadrons.Group, Sum(IIf([ReceivedMPF]<[CloseoutDate]+30,1,0)) AS OnTime, Count(Active.Counter) AS Tot, IIf([tot]=0,0,100*([OnTime]/[Tot])) AS Rate, Sum(IIf(([index]<13),1,0)) AS J_Tot, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]<13),1,0)) AS J_OnTime, IIf([j_tot]=0,0,([J_OnTime]/[J_Tot])*100) AS J_Rate, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>12 And [index]<19),1,0)) AS S_OnTime, Sum(IIf(([index]>12 And [index]<19),1,0)) AS S_Tot, IIf([s_tot]=0,0,([S_OnTime]/[S_Tot])*100) AS S_Rate, Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>18),1,0)) AS O_OnTime, Sum(IIf(([index]>18),1,0)) AS O_Tot, IIf([o_tot]=0,0,([O_OnTime]/[O_Tot])*100) AS O_Rate, [S_OnTime]+[O_OnTime] AS B_OnTime, [S_Tot]+[O_Tot] AS B_Tot, IIf([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100) AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE (((Active.[completion date]) Between [varStart] And [varStop]))

GROUP BY DutySquadrons.Group

HAVING (((DutySquadrons.Group)<>"tenant"));

2. sql2000 olap troubles

3. Help trying to do a Cross Tab Query

4. Removing Foxpro License screen for executable

5. Trying build a query using the SQL Query Tool in Enterprise Manager

6. Parameter Query

7. Cross-tab Query !!!

8. Announcing SQL-Optimizer/DBA 1.1

9. Cross-Tab Query Scenario

10. Cross Tab queries

11. Stored procedure cross tab - nested query

12. Cross Tab Query

13. Upsize Access Cross-Tab query to SQL2K