As of Oracle Database 11g, you can use the PIVOT and UNPIVOT operators to work with “crosstab” data. In a crosstab report, rows of data are displayed in separate columns. In this query, the second and third columns are the Fred Fuller columns, the fourth and fifth are the Dorah Talbot columns, and so on. In the second column, the DECODE function checks to see if the NAME column value is ‘Fred Fuller’. If it is, the calculation is performed; otherwise, a NULL is returned.
In the output, the data has been pivoted—rows of entries (such as those that contain ‘Fred Fuller’ in the NAME column) have been transformed by the DECODE function so separate rows (for separate names) are now displayed as separate columns. The following example further illustrates the pivoting by eliminating the join to the BOOKSHELF table:
The PIVOT operator simplifies the generation of this type of report.
The PIVOT query selects the data from the BOOKSHELF_CHECKOUT table and then pivots it, using the limiting conditions in the FOR clause to determine which rows to evaluate. In this case, it takes the rows for which the NAME is ‘Fred Fuller’ and calculates the maximum difference between the RETURNED_DATE and CHECKOUT_DATE for the first column, and uses the ‘Dorah Talbot’ rows for the second column.
The two queries produce the identical results—the maximum time a book was checked out for two different people, presented as separate columns in the report. The code for the PIVOT operator is simpler to read and simpler to manage. If, for example, you wanted to look at additional people’s records, modifying the PIVOT code is simple—just add the names to the IN clause and new columns will appear in the output; in the DECODE version, you need to add the code for each new column separately.
Along with a PIVOT operator, Oracle Database 11g also provides an UNPIVOT operator. As you would expect, UNPIVOT performs the opposite function, turning columns into rows. Note that UNPIVOT cannot always undo a PIVOT operation; for example, if the PIVOT operation performs an aggregate function, you cannot use UNPIVOT to generate the detail rows that were aggregated.
The following example illustrates the use of the UNPIVOT operator. In this example, five separate values are selected from the DUAL table—one for each vowel in the alphabet. Each of those selections would normally be displayed as a separate column (in this case, named V1, V2, V3, V4, and V5). The UNPIVOT operator then transforms those columns into rows for the output.
The output for this query is shown in the following listing:
You can use the PIVOT and UNPIVOT operators to transform columns into rows and back again, as shown in these examples. In combination with DECODE and CASE, these operators provide powerful tools for the display and manipulation of data.
Leave a Reply