11g New Feature: PIVOT I do have an interview tomorrow. Woohoo!

So my go at the PIVOT operator, "new" in 11g.

pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example

My example.

Let's create some data first:

CREATE TABLE transaction_types
(
  transactiontypecode VARCHAR2(10)
    CONSTRAINT pk_transactiontypecode PRIMARY KEY
);

INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );

CREATE TABLE transactions
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  transactiontypecode
    CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
    CONSTRAINT nn_ttcode_transactions NOT NULL,
  amount NUMBER(16,2)
    CONSTRAINT nn_amount_trans NOT NULL
    CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
  date_created DATE DEFAULT SYSDATE
    CONSTRAINT nn_datecreated_trans NOT NULL
);

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 1,
    'DEBIT',
    44.44 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 2,
    'DEBIT',
    20.34 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 3,
    'CREDIT',
    5.60 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 4,
    'DEBIT',
    67 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 5,
    'DEBIT',
    234.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 6,
    'CREDIT',
    76.55 );

INSERT INTO transactions
  ( id,
    transactiontypecode,
    amount )
VALUES
  ( 7,
    'DEBIT',
    3.45 );

So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.

Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:

SELECT
  TRUNC( date_created ) date_created,
  SUM( CASE
        WHEN transactiontypecode = 'DEBIT' THEN
          amount
       END ) debit_amount,
  SUM( CASE
        WHEN transactiontypecode = 'CREDIT' THEN
          amount
       END ) credit_amount
FROM transactions
GROUP BY 
  TRUNC( date_created )
ORDER BY date_created;

DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08       369.78         82.15

Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.

Here's the new cool way:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08             369.78               82.15

It happens that is 14 rows as well. Now I'll add AVG and COUNT:

SELECT *
FROM
  (
    SELECT 
      transactiontypecode tt,
      TRUNC( date_created ) date_created,
      amount 
    FROM transactions
  )
PIVOT
(
  SUM( amount ) total_amount,
  COUNT( amount ) total_count,
  AVG( amount ) avg_amount
  FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);

DATE_CREA      D_AMT      D_CNT      D_AVG      C_AMT      C_CNT      C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08     369.78          5     73.956      82.15          2     41.075

Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!

Labels: 11g, development, sql

  ¶ 7/31/2008 05:15:00 PM