Pivot and Unpivot
Present information in a spreadsheet-type crosstab report from any
relational table using simple SQL, and store any data from a crosstab table to
a relational table.
Pivot
As you know, relational tables are
tabular and they are presented in a column-value pair. Consider the case of a
table named CUSTOMERS.
Name Type
-------------------------- ---------------
CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
When this table is selected:
SELECT CUST_ID, STATE_CODE,
TIMES_PURCHASED
FROM CUSTOMERS
ORDER BY CUST_ID;
The output is:
CUST_ID STATE_CODE TIMES_PURCHASED
------- -------------- ---------------
1 CT 1
1 NY 2
2 NY 10
3 NJ 2
4 NY 4
...
and so on ...
Note how the data is
represented as rows of values: For each customer, the record shows the
customer's home state and how many times the customer purchased something from
the store. As the customer purchases more items from the store, the column
times_purchased is updated.
Now consider a case where you want to
have a report of the purchase frequency each state - that is, how many
customers bought something only once, twice, thrice and so on, from each state.
In regular SQL, you can issue the following statement:
SELECT STATE_CODE,
TIMES_PURCHASED, COUNT(1) CNT
FROM CUSTOMERS
GROUP BY STATE_CODE,
TIMES_PURCHASED;
Here is the output:
ST TIMES_PURCHASED
CNT
-- --------------- ----------
CT 0 90
CT 1 165
CT 2 179
CT 3 173
CT 4 173
CT 5 152
...
and so on ...
This is the
information you want but it's a little hard to read. A better way to represent
the same data may be through the use of crosstab reports, in which you can organize
the data vertically and states horizontally, just like a spreadsheet:
Times_purchased CT
NY
NJ ...
1 0 1 0 ...
2 23 119 37 ...
3 17 45 1 ...
...
and so on ...
Here is how you write
the query:
SELECT * FROM (
SELECT
TIMES_PURCHASED, STATE_CODE
FROM CUSTOMERS T
)
PIVOT
(
COUNT(STATE_CODE)
FOR STATE_CODE IN ('NY','CT','NJ','FL','MO')
)
ORDER BY TIMES_PURCHASED
/
Here is the output:
. TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
--------------- ---------- ---------- ---------- ---------- ----------
0 16601 90 0 0 0
1 33048 165 0 0 0
2
33151
179 0 0 0
3 32978
173 0 0 0
4 33109 173 0 1 0
... and so on ...
Syntax of the query:
...
PIVOT
(
COUNT(STATE_CODE)
FOR STATE_CODE IN ('NY','CT','NJ','FL','MO')
)
...
The second line, "for state_code ...," limits the query to only those values. This line is necessary, so unfortunately you have to know the possible values beforehand.
Note the header rows in the output:
TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
--------------- ----------
---------- ---------- ---------- ----------
The column headers are the data from the table itself: the state codes. The abbreviations may be self explanatory but suppose you want to display the state names instead of abbreviations, ("Connecticut" instead of "CT")? In that case you have to make a little adjustment in the query, in the FOR clause as shown below:
select * from (
select times_purchased as "Puchase
Frequency", state_code
from customers t
)
pivot
(
count(state_code)
for state_code in ('NY' as "New
York",'CT' "Connecticut",'NJ' "New Jersey",'FL'
"Florida",'MO' as "Missouri")
)
order by 1
/
Puchase Frequency New York Connecticut New Jersey Florida
Missouri
----------------- ---------- ----------- ---------- ---------- ----------
0 16601 90
0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
...
and so on ...
The FOR clause can
have aliases for the values there, which will become the column headers.
Unpivot
Suppose you have a spreadsheet that
shows the crosstab report shown below:
|
Purchase Frequency
|
New York
|
Connecticut
|
New Jersey
|
Florida
|
Missouri
|
|
0
|
12
|
11
|
1
|
0
|
0
|
|
1
|
900
|
14
|
22
|
98
|
78
|
|
2
|
866
|
78
|
13
|
3
|
9
|
|
...
|
.
|
|
|
|
|
Now you want to load the data into a relational table called CUSTOMERS:
SQL> desc customers
Name Type
-------------
---------------------------
CUST_ID
NUMBER(10)
CUST_NAME
VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
The spreadsheet data must be de-normalized to a relational format and then stored. Of course, you can write a complex SQL script using DECODE to load the data into CUSTOMERS table. Or you can use the reverse operation of pivot—UNPIVOT—to break up the columns to become rows, as is possible in Oracle Database 11g.
It might be easier to demonstrate this via an example. Let's create a crosstab table first, using the pivot operation:
CREATE TABLE CUST_MATRIX
AS
SELECT * FROM (
SELECT TIMES_PURCHASED AS
"Purchase Frequency", STATE_CODE
FROM CUSTOMERS T
)
PIVOT
(
COUNT (STATE_CODE)
FOR STATE_CODE IN ('NY' AS
"New York", 'CT' "Conn", 'NJ' "New Jersey", 'FL'
"Florida",
'MO' AS "Missouri")
)
You can check how the data is stored in the table:
Purchase Frequency New York Conn New Jersey Florida
Missouri
----------------- ---------- ---------- ---------- ---------- ---------
1 33048
165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4
33109 173 0 1 0
... and so on ...
This is how the data is stored in the spreadsheet: Each state is a column in the table ("New York", "Conn", and so on).
CUST_MATRIX Table Structure:
Name Type
-------------------------------- ---------------------------
Purchase Frequency NUMBER(3)
New York NUMBER
Conn NUMBER
New Jersey NUMBER
Florida NUMBER
Missouri NUMBER
You need to break up the table so that rows will show only the state code and the counts for that state. This can be done by the unpivot operation shown below:
SELECT *
FROM CUST_MATRIX
UNPIVOT
(
STATE_COUNTS
FOR STATE_CODE IN ("NEW
YORK","CONN","NEW
JERSEY","FLORIDA","MISSOURI")
)
ORDER BY "Purchase
Frequency", STATE_CODE
Here is the output:
Purchase Frequency STATE_CODE STATE_COUNTS
---------------- ----------- ------------
1 Conn 165
1 Florida 0
1 Missouri 0
1 New Jersey 0
1 New York 33048
2 Conn 179
2 Florida 0
2 Missouri 0
...
and so on ...
Note how the each
column name has become a value in the STATE_CODE column. How did Oracle know
that STATE_CODE is a column name? It knew that from the following clause in the
query:
FOR STATE_CODE IN ("NEW
YORK","CONN","NEW
JERSEY","FLORIDA","MISSOURI")
Here you specified that the values "New York", "Conn", and so on are values of a new column you want to be unpivoted on, called state_code. Look at part of the original data:
Purchase Frequency New York Conn New Jersey Florida
Missouri
----------------- ---------- ---------- ---------- ---------- - ---------
1 33048 165 0 0 0
As the column "New York" suddenly became a value in a row, how would you show the value 33048, under which column? That question is answered by the clause just above the for clause inside the unpivot operator in the above query. As you specified STATE_COUNTS, that is the name of the new column created in the resultant output.
Here is the slightly modified code to generate vowels of the English alphabet:
Select value
from
(
(
select
'a' v1,
'e' v2,
'i' v3,
'o' v4,
'u' v5
from dual
)
unpivot
(
value
for value_type in
(v1,v2,v3,v4,v5)
)
)
/
Here is the output:
V
-
a
e
i
o
u
