Donald K. Burleson Oracle Tips

Coding a matrix of display values in Oracle SQL

In an earlier tip we showed you how to use the sum and decode functions to count the number of distinct values in a low cardinality column.

We can now take this same SQL and enhance it to display the counts of column values in a two-dimensional format. 

Oracle has a fascinating non-ANSI feature called an in-line view.  The in-line view allows for a query to be placed into the FROM clause of an SQL statement, where you would normally place a table name.

By performing a SELECT in the FROM clause, we can summarize two columns, and display a third value based on two other columns.

In the example below, we take the employee name (ename) and the department number (deptno) and display the salary of the employee by their department.

prompt Display of Salary by Department

SELECT 

   *

FROM  (SELECT

         ename,

         sum(decode(deptno,10,sal)) DEPT10,

         sum(decode(deptno,20,sal)) DEPT20,

         sum(decode(deptno,30,sal)) DEPT30,

         sum(decode(deptno,40,sal)) DEPT40

       FROM

         emp

       GROUP BY

         ename)

ORDER BY 1;

Display of Salary by Department

ENAME          DEPT10     DEPT20     DEPT30     DEPT40

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

ADAMS                       1100

ALLEN                                  1600

BLAKE                                  2850

CLARK            2450

FORD                        3000

JAMES                                   950

JONES                       2975

KING             5000

MARTIN                                 1250

MILLER           1300

SCOTT                       3000

SMITH                        800

TURNER                                 1500

WARD                                   1250

This type of query is very useful for Oracle data warehouse system where you must display information from a FACT table according to the values of two other columns.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm