Thursday, January 25, 2007

REPOST: Pivot and Crosstab Queries

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

This page is powered by Blogger. Isn't yours?