devxlogo

Create Flexible Cross-tab Queries without Using Temporary Tables

Create Flexible Cross-tab Queries without Using Temporary Tables

Why Trust Us

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  Why ChatGPT Is So Important Today
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

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
View Author
Show More