SQL: a Cross-Tabular Report with Case, Rollup and Grouping Functions

A Cross-Tabular Report is widely used in computer software. If you keep a journal of expenses, for instance, and by the end of year, you would like to review how much you have spent on each type of goods in a month-by-month view. Or a website administrator would like to know, for each page or URL of website, how many visitors visited using different browsers. These are cases when you need a cross-tabular report. (Definitions of Cross Tabulation or Contingency table.)

So how to generate a cross tab report in just one line of SQL? The idea is to split columns with "CASE" and group rows with "GROUP BY ROLLUP(name)" and decorate the result with "DECODE(GROUPING(name), 1, 'Total', name)".

Here's an Example:

We have a log table:

SQL> select page "Page", brws_type "Browser" from visit_table;

Page            Browser

--------------- ----------

index.htm       FF

index.htm       IE

index.htm       IE

page1.htm       FF

page2.htm       FF

page3.htm       FF

about.htm       FF

about.htm       SF

index.htm       SF

index.htm       SF

index.htm       FF

Page            Browser

--------------- ----------

page2.htm       IE

page2.htm       IE

contact.htm     IE

contact.htm     SF

page3.htm       SF

And now comes the query for cross-tabular report.

SQL> SELECT DECODE(GROUPING(page), 1, 'All pages', page) "Pages",
            COUNT(CASE WHEN brws_type='FF' THEN 1 ELSE null END) "Firefox",
            COUNT(CASE WHEN brws_type='IE' THEN 1 ELSE null END) "Internet Explorer",
            COUNT(CASE WHEN brws_type='SF' THEN 1 ELSE null END) "Safari",
            COUNT(*) count
      FROM visit_table
      WHERE 1=1
      GROUP BY ROLLUP(page)
      ORDER BY count desc;
Pages              Firefox Internet Explorer     Safari      COUNT
--------------- ---------- ----------------- ---------- ----------
All pages                6                 5          5         16
index.htm                2                 2          2          6
page2.htm                1                 2          0          3
about.htm                1                 0          1          2
contact.htm              0                 1          1          2
page3.htm                1                 0          1          2
page1.htm                1                 0          0          1

SQL is a powerful language.