Oracle Pivot Operator

While working on complex reports, we occasionally have a need to transform Columns to Rows and vice versa. Converting Rows to Columns is typically called Pivot and the opposite, converting Columns to Rows is called Unpivot. Concept of Pivot, and the need for it, has been around for a long time. Excel for example, has a Pivot table to do these type of data transformations. It’s been available in SQL Server for a while. Pivoting is sometimes called Crosstab. PowerBuilder has a datawindow style like that. These are very useful in building data warehouses, reports etc.

Coming back to the topic, Oracle introduced the operators recently (in 11g). Before that you would have to do a round about way. There are several ways to do this, but here is a simple way, when # of columns are small.

CREATE TABLE payment_list(pay_type VARCHAR2(3), description VARCHAR2(30), amount NUMBER);
INSERT INTO payment_list VALUES('CHK', 'Check', 200);
INSERT INTO payment_list VALUES('CRE', 'Credit Card', 500);
INSERT INTO payment_list VALUES('DEB', 'Debit Card', 210);
INSERT INTO payment_list VALUES('ACH', 'ACH payments', 2000);
INSERT INTO payment_list VALUES('CHK', 'Check', 700);

Here is what we get, with a normal SELECT:

DESCRIPTIONAMOUNT
Check
200
Credit Card
500
Debit Card
210
ACH payments
2000
Check
700

PAY_TYPE
CHK CRE DEB ACH CHK

Each row has a payment type and the amount paid. Sometimes, you will want to see these in a column:

SELECT Decode(pay_type, 'CHK', amount, NULL) AS check_pmt,
        Decode(pay_type, 'CRE', amount, NULL) AS cc_pmt,
        Decode(pay_type, 'DEB', amount, NULL) AS debit_pmt,
        Decode(pay_type, 'ACH', amount, NULL) AS ach_pmt
    FROM payment_list;
CHECK_PMT CC_PMT DEBIT_PMT ACH_PMT
200
500
210
2000
700

By using Oracle Decode function, we separated the amount for each type into separate columns. Of course, in reality, we will have lot more rows for each type and we may want to see the Total for each type:

SELECT Decode(pay_type, 'CHK', Sum(amount), NULL) AS check_pmt,
<span style="color: #0000ff;">    Decode(pay_type, 'CRE', Sum(amount), NULL) AS cc_pmt,
Decode(pay_type, 'DEB', Sum(amount), NULL) AS debit_pmt,
Decode(pay_type, 'ACH', Sum(amount), NULL) AS ach_pmt
FROM payment_list
GROUP BY pay_type;</span>
CHECK_PMT CC_PMT DEBIT_PMT ACH_PMT
900
210
2000
500

We can do the same thing with the new Pivot operator:

SELECT * FROM (
	SELECT pay_type, amount
	FROM payment_list)
		PIVOT (Sum(amount)
		FOR pay_type IN ('CHK', 'CRE', 'DEB', 'ACH')
		);
‘CHK’ ‘CRE’ ‘DEB’ ‘ACH’
900 500 210 2000

 

By the way, if you wanted to collapse your DECODE SQL to return single row result like above, you would use:

SELECT Sum(check_pmt) CHK, Sum(cc_pmt) CC, Sum(debit_pmt) DEB, Sum(ach_pmt) ACH FROM 
SELECT Decode(pay_type, 'CHK', Sum(amount), NULL) AS check_pmt,
Decode(pay_type, 'CRE', Sum(amount), NULL) AS cc_pmt,
Decode(pay_type, 'DEB', Sum(amount), NULL) AS debit_pmt,
Decode(pay_type, 'ACH', Sum(amount), NULL) AS ach_pmt
FROM payment_list
GROUP BY pay_type
)

 

Pivot operator is available in many modern databases (and Excel too). I couldn’t find a site that shows Oracle syntax for PIVOT operation in a simple format yet. However, Oracle’s site has great content on the operator (and unpivot) here.  For sake of completeness, I am including the SQL syntax Pivot operator in SQL Server. The only difference, I could see here is SQL Server’s usage of square brackets instead of quotes.

SELECT &lt;non-pivoted column&gt;,
	[ pivoted column] AS &lt;column name&gt;,
	[ pivoted column] AS &lt;column name&gt;,
	...
	[ pivoted column] AS &lt;column name&gt;
FROM(&lt;SELECT query that produces the data&gt;) AS &lt;alias for the source query&gt;
PIVOT
(
    &lt;aggregation function&gt;(column)
    FOR
    [&lt;column that contains the values that will become column headers&gt;]
    IN ( [pivoted column], [pivoted column],
    ... [pivoted column])
AS &lt;alias for the pivot table&gt;
[ORDER BY clause];

That’s all folks. I know, it has a weird syntax, but The inner most SQL (SELECT query that produces the data), is what you are pivoting. The row(s) for each value in the FOR .. IN list will be converted to a column. So, when the inner SQL finishes, you will get the columns CHK, CRE, DEB, ACH. SELECT * in the outer SQL, makes sure all these columns are picked up.

To be contd…

Advertisements

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s