Total Pageviews

June 1, 2015

6/01/2015 02:06:00 AM
1

Oracle 11g concepts

In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software.
Among other functions, a pivot table can automatically sort, count total or give the average of the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.

What is Pivot Table in Oracle ?

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.
Syntax:
SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
---
Example:
Old versions Prior to 11g
 SELECT   job,
           SUM(DECODE(deptno, 10, sal, 0)) AS deptno10,
           SUM(DECODE(deptno, 20, sal, 0)) AS deptno20,
           SUM(DECODE(deptno, 30, sal, 0)) AS deptno30,
           SUM(DECODE(deptno, 30, sal, 0)) AS deptno30
              FROM   emp
   WHERE   deptno IN (10, 20, 30, 40)
GROUP BY   job
JOB     deptno10       deptno20        deptno30      deptno30
Others                             501  
Clerk   1300                   1900                    950

Pivot Table  Equilvalent:

WITH pivot_data AS (
    SELECT deptno, job, sal
     FROM   emp
         )
    SELECT *
FROM   pivot_data
   PIVOT (
 SUM(sal)        --<-- pivot_clause
   FOR deptno          --<-- pivot_for_clause
    IN  (10,20,30,40)   --<-- pivot_in_clause
);
OUTPUT
JOB     10         20         30      40
Others            501  
Clerk   1300   1900    950
WITH pivot_data AS (
    SELECT deptno, job, sal
     FROM   emp
         )SELECT *
   FROM   pivot_data
    PIVOT (SUM(sal)   AS sum
    ,      COUNT(sal) AS cnt
    FOR   (deptno,job) IN ((30, 'SALESMAN') AS d30_sls,
                                                  (30, 'CLERK')    AS d30_clk));

D30_SLS_SUM D30_SLS_CNT T D30_CLK_SUM D30_CLK_CNT
----------- ----------- ----------- ----------- ----------- -----------
       6160                  4                                   1045                      1