How To : Displaying Column Data Horisontally

Some time you need to create a result set where the rows need to be columns, or vice versa. This commonly requirement can be done by using pivot (or crosstab) query.

1. A simple pivot query can be done by doing the following
2. Add some kind of count or row number to your query, if necessary for the grouping
3. Then use your (revised) original query as a sub-query
4. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
5. 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 an example of a pivot query. Say you have the following set of data:


And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40.
We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are
currently in the count column. It would look like this:

Note: 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).