OracleBlog

Here is another advanced concept that will come in useful when solving Oracle problems.

Imagine you're trying to create a result set where the rows need to be columns, or vice versa. In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done.

As always, when you want to understand something, you can start by Asking Tom.

A simple pivot query is accomplished by basically doing the following:
1. Add some kind of count or row number to your query, if necessary for the grouping
2. Then use your (revised) original query as a sub-query
3. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
4. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.
(Note: it gets more complicated if you don't know how many columns you'll need).

Here is another one of Ask Tom's examples. It clearly shows how you use decode to create a "sparse" matrix, and then use max to "squash" it down.

Let's look a simple example in slow motion.

Here's the data
CREATE TABLE CFL (season NUMBER(4), team VARCHAR2(16), points NUMBER(3));
INSERT INTO CFL (season, team, points) VALUES (2004, 'Argonauts', 21);
INSERT INTO CFL (season, team, points) VALUES (2004, 'Alouettes', 28);
INSERT INTO CFL (season, team, points) VALUES (2004, 'Tiger-Cats', 19);
INSERT INTO CFL (season, team, points) VALUES (2004, 'Renegades', 10);
INSERT INTO CFL (season, team, points) VALUES (2003, 'Argonauts', 18);
INSERT INTO CFL (season, team, points) VALUES (2003, 'Alouettes', 26);
INSERT INTO CFL (season, team, points) VALUES (2003, 'Tiger-Cats', 2);
INSERT INTO CFL (season, team, points) VALUES (2003, 'Renegades', 14);
INSERT INTO CFL (season, team, points) VALUES (2002, 'Argonauts', 16);
INSERT INTO CFL (season, team, points) VALUES (2002, 'Alouettes', 27);
INSERT INTO CFL (season, team, points) VALUES (2002, 'Tiger-Cats', 15);
INSERT INTO CFL (season, team, points) VALUES (2002, 'Renegades', 10);

What we want:
A table showing each of these 4 teams and their point tables for these 3 seasons.

So what is our pivot row/column? Season.

Step 1/2: We are using season, so we don't need to create our own grouping field, like count, rownum, or running total (sum) for example. That would be easy enough to do, but let's keep this simple.

Step 3: Use "decode" to turn the season row into a column. Take a look at our "sparse" matrix.

SELECT team,
DECODE (season, 2002, points, NULL) Yr2002,
DECODE (season, 2003, points, NULL) Yr2003,
DECODE (season, 2004, points, NULL) Yr2004
FROM (SELECT season, team, points FROM CFL);

TEAM                 YR2002     YR2003     YR2004
---------------- ---------- ---------- ----------
Argonauts                                      21
Alouettes                                      28
Tiger-Cats                                     19
Renegades                                      10
Argonauts                           18
Alouettes                           26
Tiger-Cats                           2
Renegades                           14
Argonauts                16
Alouettes                27
Tiger-Cats               15
Renegades                10

Step 4: Now let's use max to "squash" this into single rows. Don't forget GROUP BY.

SELECT team,
MAX (DECODE (season, 2002, points, NULL)) Yr2002,
MAX (DECODE (season, 2003, points, NULL)) Yr2003,
MAX (DECODE (season, 2004, points, NULL)) Yr2004
FROM (SELECT season, team, points FROM CFL)
GROUP BY team;

TEAM                 YR2002     YR2003     YR2004
---------------- ---------- ---------- ----------
Alouettes                27         26         28
Argonauts                16         18         21
Renegades                10         14         10
Tiger-Cats               15          2         19

Pretty cool, eh? Easy, too.

Notice that the key to this is DECODE. If DECODE is not already part of your toolbelt, I recommend studying up.

Ready for a tougher example? Let's look at another Ask Tom.

For further study of pivot queries and analytic functions in general, there is an awesome write-up in Chapter 12 of Tom Kyte's "Expert One-on-One Oracle." You'd think I'd get a kickback from Tom Kyte with all the promotion I'm doing, but the honest truth is that no one explains it as well as he.

So, do you understand pivot queries now? No problems?

If so, now you're ready for one of Ask Tom's more complex examples.

Pivot Tables

One final word: don't confuse pivot queries with pivot tables. Pivot tables are a different concept, and have different uses (most typically to fill in missing data). Until I blog about pivot tables, check out these two links:

Jonathan Gennick Updated
Laurent Schneider

Originally posted September 1, 2005