We’ll extend the report to display one column per type. Now, I’m not a glutton for punishment and don’t particularly like typing. Whenever possible, I like to write programs to do the heavy lifting for me. You can use a SQL statement to generate all of the column expressions automatically like this:

select      distinct
   type + “ = CASE t.type WHEN ‘“ + type + “‘ THEN s.qty ELSE 0 END,”
from    titles

UNDECIDED    = CASE t.type WHEN ‘UNDECIDED   ‘ THEN s.qty ELSE 0 END,
business     = CASE t.type WHEN ‘business    ‘ THEN s.qty ELSE 0 END,
mod_cook     = CASE t.type WHEN ‘mod_cook    ‘ THEN s.qty ELSE 0 END,
popular_comp = CASE t.type WHEN ‘popular_comp’ THEN s.qty ELSE 0 END,
psychology   = CASE t.type WHEN ‘psychology  ‘ THEN s.qty ELSE 0 END,
trad_cook    = CASE t.type WHEN ‘trad_cook   ‘ THEN s.qty ELSE 0 END,

The more data elements you are dealing with, the more likely it is that this approach will be useful for you. Sometimes you’ll need to try something a couple of times, and it may take a little pruning when it’s finished, but it sure beats typing it all in!

You can use pairs of single or double quotes freely in SQL Server (just match them correctly). I took advantage of that fact to insert quotes around the value of type after WHEN.

The next step in building the crosstab is to build the raw data from which the totals will be generated. Cut the six case expressions you just generated out of the Result tab and paste them into the Query tab in place of the two (business and non-business) expressions you were using before. You should end up with a query that looks like this:

select
     t.type,
     s.qty,
UNDECIDED    = CASE t.type WHEN ‘UNDECIDED   ‘ THEN s.qty ELSE 0 END,
business     = CASE t.type WHEN ‘business    ‘ THEN s.qty ELSE 0 END,
mod_cook     = CASE t.type WHEN ‘mod_cook    ‘ THEN s.qty ELSE 0 END,
popular_comp = CASE t.type WHEN ‘popular_comp’ THEN s.qty ELSE 0 END,
psychology   = CASE t.type WHEN ‘psychology  ‘ THEN s.qty ELSE 0 END,
trad_cook    = CASE t.type WHEN ‘trad_cook   ‘ THEN s.qty ELSE 0 END
  from   titles  t  inner  join  sales  s
       on   t.title_id  =  s.title_id

Go ahead and execute the query.

type    qty UNDECIDED business mod_cook popular_comp psychology trad_coo
----------- --- -------- -------- ------- ----------- --------- --------
business     5  0        5        0       0           0         0
business     10 0        10       0       0           0         0
business     25 0        25       0       0           0         0
business     35 0        35       0       0           0         0
business     15 0        15       0       0           0         0
mod_cook     10 0        0        10      0           0         0
mod_cook     25 0        0        35      0           0         0
mod_cook     15 0        0        15      0           0         0
popular_comp 30 0        0        0       30          0         0
popular_comp 50 0        0        0       50          0         0
psychology   20 0        0        0       0           20        0
psychology   3  0        0        0       0           3         0
psychology   75 0        0        0       0           75        0
psychology   10 0        0        0       0           10        0
psychology   20 0        0        0       0           20        0
psychology   25 0        0        0       0           25        0
psychology   15 0        0        0       0           15        0
psychology   25 0        0        0       0           25        0
trad_cook    40 0        0        0       0           0         40
trad_cook    20 0        0        0       0           0         20
trad_cook    20 0        0        0       0           0         20

I’m limited to 80 columns, so I had to trim the report a little bit here and there; you’re almost done. Each sale is reflected only in the proper column. All we need to do now is show dollar sales instead of unit sales, and total the columns up by stor_id. To show dollar sales, just change the case expression to include s.qty * t.price, like this:

business  = CASE t.type WHEN ‘business ‘ THEN s.qty * t.price ELSE 0 END,

To total by stor_id, use sum() with GROUP BY.

select
      s.stor_id,
      Total = sum(s.qty * t.price),
   UNDECIDED    = sum(CASE t.type WHEN ‘UNDECIDED   ‘
                      THEN s.qty * t.price ELSE 0 END),
   business     = sum(CASE t.type WHEN ‘business    ‘
                      THEN s.qty * t.price ELSE 0 END),
   mod_cook     = sum(CASE t.type WHEN ‘mod_cook    ‘
                      THEN s.qty * t.price ELSE 0 END),
   popular_comp = sum(CASE t.type WHEN ‘popular_comp’
                      THEN s.qty * t.price ELSE 0 END),
   psychology   = sum(CASE t.type WHEN ‘psychology  ‘
                      THEN s.qty * t.price ELSE 0 END),
   trad_cook    = sum(CASE t.type WHEN ‘trad_cook   ‘
                      THEN s.qty * t.price ELSE 0 END)
  from titles t inner join sales s
        on  t.title_id  =  s.title_id
group  by  s.stor_id

stor_id Total UNDECIDED business mod_cook popular_comp psychology trad_coo
------- -------- -------- -------- -------- ----------  --------- --------
6380    147.86   0.00     99.95    0.00    0.00        47.91     0.00
7066    2,197.75 0.00     0.00     0.00    1,000.00    1,197.75  0.00
7067    1,536.50 0.00     0.00     0.00    0.00        159.70    1,376.80
7131    1,500.55 0.00     0.00     74.75   0.00        1,425.80  0.00
7896    604.40   0.00     404.50   199.90  0.00        0.00      0.00
8042    1,232.00 0.00     498.65   44.85   688.50      0.00      0.00

Mission accomplished. Notice that the SUM() function wraps around the entire case expression. Also, the report is fairly efficient; it requires only a single join of the sales and titles tables (other methods would require one join per column). In addition, my informal tests have shown that the case operator has very little CPU overhead.