PIVOT in Oracle 11G to Select Rows As Columns

In this article, we will learn: how to display rows as columns in oracle.

Before 11G, the solution to the pivot problem was with a combination of max and decode but in 11g, with the PIVOT syntax, the same query can be written in a more compact, readable form.

Let’s take an example to understand it better.

CONTACT table 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 data we are looking for:

This is the result set we want:

Before 11g solution(using Decode and Max):

using PIVOT solution:

© 2015, All rights reserved.