Introduction

The turning of rows to columns is a common requirement. The user can write a dynamic SQL query to turn rows to the column and vice-versa. Most of the database has some inbuilt function which helps to achieve this requirement. The Oracle 11g has a clause which helps to write SQL query to turn rows to the columns and vice-versa. Please find the functions below –

PIVOT: This clause used to convert rows into the columns.
UNPIVOT: This clause used to convert columns into the rows.

Examples and Sample SQL

The pivot clause helps to convert rows into the columns representation or in another term we can say in tabular form. The pivot is a highly used function in excel sheet as well. It helps to convert data into a various tabular format.
The Oracle provide pivot clause which helps to accomplish the short of the same task. Suppose, we have one table ADDRESSELEMENT in a relational database which store records based on ADDRESSID and which can repeat multiple time based on the data availability.
It stores the information related to Address Line 1, Address Line 2, City, Zip, State & County like below given –

The above screenshot has the sample data which need to convert into the columns like ADDRESSID, ADD1, ADD2, ADD3, CITY, STATE, USCOUNTY & ZIP.

The user can use PIVOT clause to achieve the above requirement.