A frequent requirement in SQL is to “pivot” a result set – that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.
This post shows the use of PIVOT with an example and sample scripts.
Let’s take a table CONTACT that stores contact details of persons – landline numbers, mobile numbers, website URLs. A person can have multiple contacts of each type, and each contact type has a priority associated with it.
The requirement is to display the primary contact (priority = 1) of each type – Landline, Mobile, Website – as columns.
This is the entire data in the table CONTACT:
This is the data we are interested in:
And this, the result set we want:
Pre 11G: Using DECODE
Before 11G, the popular solution to the pivot problem was with a combination of max and decode.
In 11G: Using PIVOT
With the PIVOT syntax, the same query can be written in a more compact, readable form.
Notes and References
While PIVOT queries do look a lot more intuitive than their pre-11G alternatives, the restriction of having to hard-code the pivot values still remains. If a subquery is used instead of a fixed list in the pivot IN clause, Oracle raises an error.
This restriction is relaxed in case the result set is generated in XML format with the “XML” keyword – this article gives details about its use. You will also find there more examples of pivoting with column aliases, pivoting on multiple columns, and performance analysis of pivot queries.