Create Flexible Cross-tab Queries without Using Temporary Tables

It’s very easy to create cross-tab queries in SQL. In the SELECT statement of a query, most databases allow you to use an expression with an aggregate function, not only a field name. In access use this syntax:

 SELECT Sum(Iif(District='US', Sales, 0)) as SalesUS,Sum(Iif(District='Europe', Sales, 0)) as SalesEurope _ FROM tblSales

Here’s an example for IBM DB/2 (notice the column “other”):

 Select SUM(CASE WHEN district='US' THEN sales ELSE 0 _END) AS US         , SUM(CASE WHEN district='Europe' THEN sales _ELSE 0 END) ASEurope         , SUM(CASE WHEN district NOT IN ('US', 'Europe')_ THEN sales ELSE0END) AS OtherFROM mdt.sales

This also works with MS-SQL Server. In Oracle, use the “DECODE” statement.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: