Helpful ideas and solutions for the Oracle application enthusiast. Check out the archives

Thursday, September 01, 2005

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.

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 grouping2. Then use your (revised) original query as a sub-query3. 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).

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.

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:

The only problem with this is you have to know your columns in advance. Now I know Tom would say that SQL requires this, that it's a limitation of SQL... Yeah right. Since when did SQL ever stop Oracle from adding new syntax that would further lock in its grateful customers. :)

This is a terrible paradigm that we are stuck with from a legacy system that we must query. What has to happen is that these values must appear in our table but a new row would have to be created for all changes that occur at the same time (i.e. update_dt is the same)

So in the audit example we have the first two rows that would share a change in one row. The next three would be one change per row.

In the end we would have the values replaced if the "column_name" is the same of the column being selected and the issue_no is the same and the new_value is that of the value in the column. (column name)

Extremely tricky. For me anyway. I cannot seem to produce a way to do this no matter what I try there is a caveat that hinders my goal.

NOTE:: 1)Number of visits may vary for each subject in different project 2) There are many fieldnames(like VSN1N,STDSBP1N etc..) stored under fieldname 3) For each fieldname fieldval is stored under fieldval The tool I am using does not support pl/sql and does not allow me to create any temp. tables.I must use SQL.

Hi Robert,Thanks for the blog, very clear and precise explanation in lay-mans terms... Just the way I like it!I noticed the links to Tom Kytes web site are broken (probably due to Toms revised web site). I presume the first link referes to....http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740

this is a simple example .... my real life scenario is compliated with more fields and more combinations .... So something like using substr(dept,1,2) won't work ..I will post sqls to create table and data in the next post