Feb 21 2010 8:19AM GMT

Migrating from 9i to 11g – Pivot and Unpivot

Posted by: Lakshmi Venkatesh

Uncategorized

This feature is available form Oracle 11g

It is a Data warehouse feature that can be used in PL/SQL programs. It is more useful to represent the data in cross tabular format.

Pivot – Enables to transfer rows to columns. It enables to use aggregate function – multiple rows of input is always consolidated into smaller sets. It enhances the performance rather than using any other way of rows to columns conversion. Also, it reduces network load.

Unpivot – Does a column to row conversion

Pivot

Requirement -

CREATE TABLE ACCOUNT (customer_no number, account_details varchar2(30), amount number, branch varchar2(50))

INSERT INTO ACCOUNT VALUES (1, ‘Savings’, 5000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (2, ‘Savings’, 10000, ‘Novena’);
INSERT INTO ACCOUNT VALUES (3, ‘Savings’, 60000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (4, ‘Current’, 75000, ‘Orchard’);
INSERT INTO ACCOUNT VALUES (5, ‘Current’, 30000, ‘Newton’);
INSERT INTO ACCOUNT VALUES (6, ‘Current’, 25000, ‘Novena’);

Lets look at the Data set first -

Customer No Account_details Branch Amount
1 Savings Orchard 5000
2 Savings Novena 10000
3 Savings Orchard 60000
4 Current Orchard 75000
5 Current Newton 30000
6 Current Novena 25000

To be converted as follows – Display branch wise sum of amounts for accounts

Account_details Orchard Novena Newton
Savings 65000 10000
Current 75000 25000 30000

I have had this requirement many many times !!

Lets see how we can achieve this in Oracle 9i-

To achive the same functionality – just close not exact !

To achive the same functionality

Oracle 9i
Select distinct account_Details,
Case when branch = ‘Orchard’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Orchard,
Case when branch = ‘Newton’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Newton,
Case when branch = ‘Novena’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Novena
FROM account
Order by account_details DESC

ACCOUNT_DETAILS ORCHARD NEWTON NOVENA
—————————— ———- ———- —————————————-
Savings 65000
Savings 10000
Current 75000
Current 30000
Current 25000
—————————— ———- ———- —————————————-

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 342 | 5 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 342 | 4 (50)| 00:00:01 |
| 2 | WINDOW SORT | | 6 | 342 | 5 (60)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ACCOUNT | 6 | 342 | 2 (0)| 00:00:01 |
——————————————————————————-

** The above is just close to the PIVOT output.

Oracle 11g
PIVOTING on Single column

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount)
FOR branch IN (‘Orchard’, ‘Newton’, ‘Novena’))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ‘Orchard’ ‘Newton’ ‘Novena’
—————————— ———- ———- ———-
Savings 65000 10000
Current 75000 30000 25000

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 3260125471

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 342 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY PIVOT| | 6 | 342 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ACCOUNT | 6 | 342 | 2 (0)| 00:00:01 |
——————————————————————————-

****************************************************************************

To display the output based on branch

Select distinct Branch,
Case when account_details = ‘Savings’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Savings,
Case when account_details = ‘Current’ THEN
SUM(amount) OVER (PARTITION BY branch, account_details)
END Current
FROM account
Order by branch DESC

BRANCH SAVINGS CURRENT
————————————————– ———- ———-
Orchard 65000
Orchard 75000
Novena 10000
Novena 25000
Newton 30000

Oracle 11g – using Pivot function

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount)
FOR account_details IN (‘Savings’, ‘Current’))
ORDER BY branch DESC;

BRANCH ‘Savings’ ‘Current’
————————————————– ———- ———-
Orchard 65000 75000
Novena 10000 25000
Newton 30000

***************************************************************************

PIVOTING on Multiple column

SELECT *
FROM
(SELECT account_details, branch , amount, customer_no
FROM account) PIVOT (sum(amount)
FOR (customer_no, branch) IN ((1,’Orchard’) as orchard, (2, ‘Newton’) as Newton, (3, ‘Novena’) Novena))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ORCHARD NEWTON NOVENA
—————————— ———- ———- ———-
Savings 5000
Current

PIVOTING on Multiple agregations

(this is just for example only – just to show it can be agregated based on multiple columns)

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account) PIVOT (sum(amount) amt, count(amount) cnt
FOR branch IN (‘Orchard’, ‘Newton’, ‘Novena’))
ORDER BY account_details DESC;

ACCOUNT_DETAILS ‘Orchard’_AMT ‘Orchard’_CNT ‘Newton’_AMT ‘Newton’_CNT ‘Novena’_AMT ‘Novena’_CNT
—————————— ————- ————- ———— ———— ———— ————
Savings 65000 2 0 10000 1
Current 75000 1 30000 1 25000 1
—————————— ————- ————- ———— ———— ————

PIVOTING on NULLs

INSERT INTO ACCOUNT VALUES (7, ‘Savings’, null, ‘Newton’);

**NULLS are handled automatically

PIVOT values XML

SET LONG 1024;
SELECT *
FROM
(SELECT account_details, branch , amount
FROM account
) PIVOT XML (SUM(amount) FOR branch IN (ANY) )

Output

ACCOUNT_DETAILS
——————————
BRANCH_XML
——————————————————————————–

Current
Newton30000Novena25000Orchard75000
Savings
NewtonNovena10000Orchard65000

PIVOTING Using SUBQUERIES

create table branch (branch_nm varchar2(50));

INSERT INTO BRANCH VALUES (‘Orchard’);
INSERT INTO BRANCH VALUES (‘Newton’);
INSERT INTO BRANCH VALUES (‘Novena’);

SELECT *
FROM
(SELECT account_details, branch , amount
FROM account
) PIVOT XML(SUM(amount)
FOR branch IN (SELECT branch_nm
FROM branch));

ACCOUNT_DETAILS BRANCH_XML
—————————————————————————————————–
Current Newton30000Novena25000Orchard75000

Savings NewtonNovena10000Orchard65000

****************************************************************************

Unpivot

**Note – Agregations done by PIVOT function cannot be reversed by UNPIVOT.
33.2 Unpivot
Requirement –

Data set

Account_details Orchard Novena Newton
Savings 65000 10000
Current 75000 25000 30000

To be converted as follows – Display branch wise sum of amounts for accounts

Account_details Branch Amount
Savings Orchard 65000
Savings Novena 10000
Current Orchard 75000
Current Newton 30000
Current Novena 25000

UNPIVOTING

Create table account
(Account_details varchar2(50),
Orchard number,
Novena number,
Newton number)

INSERT INTO ACCOUNT VALUES (‘Savings’, 65000,10000,NULL);

INSERT INTO ACCOUNT VALUES (‘Current’,75000,25000,30000);

SELECT *
FROM account
UNPIVOT (amount For Branch IN (Orchard, Newton, Novena))
ORDER BY account_Details DESC, branch

ACCOUNT_DETAILS BRANCH AMOUNT
————————————————– ——- ———-
Savings NOVENA 10000
Savings ORCHARD 65000
Current NEWTON 30000
Current NOVENA 25000
Current ORCHARD 75000

UNPIVOTING Multiple columns

SELECT *
FROM account_1
UNPIVOT (amount FOR (account_details,branch) IN
( Savings AS (‘Savings’, ‘Orchard’),
Current AS (‘Current’, ‘Novena’) ) )
ORDER BY account_details DESC, branch

UNPIVOTING ON MULTIPLE AGGREGATIONS

SELECT *
FROM account2
UNPIVOT ((amount, quantity)
FOR channel IN ((Direct_sumq, Direct_suma) AS 3,
(Internet_sumq, Internet_suma) AS 4 ))
ORDER BY quantity DESC, branch;