devxlogo

Create Flexible Cross-tab Queries without Using Temporary Tables

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.

See also  lenso.ai - an AI reverse image search platform
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist