How to Convert rows into columns using SQL in Oracle

What comes in mind if we think about transposition like row-to-column or column-to-row. Answer is very simple, It’s ‘Pivot’ when there is row-to-column transposition and it’s ‘Unpivot’ when there is column-to-row transposition. As post title shows this article is about row-to-column transposition i.e. pivot.

Why Row-TO-Column Conversion (Transposition)?

Sometimes our requirement is like we need to take separate row results as input and put them on single column or multiple columns. We will see the different situations where we need Row-TO-Column conversion in later posts.

Setup of sample data for this demonstration:

For demonstration purpose we are going to create two tables as TABLE_CATEGORY and TABLE_PRODUCT with few records. Download this sample data.

Now we are ready with necessary data for our demonstration.

How to accomplish this transposition using SQL in Oracle?

Below are some of the ways by using which we can convert rows into columns.

Using DECODE Transformation

Using Oracle 11g SQL Pivot

Using SQL CASE Operator

Using Oracle 9i xmlagg Function

Using SQL Within Group function and Oracle 11g

We will see each of above mention method one by one.

1. Convert rows into columns using DECODE Transformation:

Suppose, we want to find out category names for the products ‘DTHVoucher Rs 500′, ‘E-RECHARGE’ and ‘SMARTPHONEV6700′. We can get these category names by simply executing below query.

But we want this output in multiple columns instead of rows. We can achive this by using DECODE transformation using below steps.Step 1 :By adding DECODE function in above query. Modified query and its output is below.

From above output we can see that, each column is having three rows and out of those three rows, only one row is having category name and ohter two rows are NULL. Here we can use MAX function to get desired output. So below is the modified query using MAX function with result.

2. Convert rows into columns using Pivot Transformation:

We can use Pivot for row to column transformation as shown in below query. Here inner query returns the the categories corresponding to products ‘E-RECHARGE’ and’SMARTPHONEV6700′.
By using PIVOT operator we are transposing those categories in column.