Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Nov 29, 2001

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) AS
Europe
         , SUM(CASE WHEN district NOT IN ('US', 'Europe')_
 THEN sales ELSE
0
END) AS Other
FROM mdt.sales

This also works with MS-SQL Server. In Oracle, use the "DECODE" statement.
John Sevarts
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date