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. Click Here for downloading this sample data.
Now we are ready with necessary data for our demonstration.

How to accomplish this transposition using SQL in Oracle?

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

1. Using DECODE Transformation
2. Using Oracle 11g SQL Pivot
3. Using SQL CASE Operator
4. Using Oracle 9i xmlagg Function
5. Using SQL Within Group function and Oracle 11g
6. using SYS_CONNECT_BY_PATH Operator

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

1. Row-To–Column Conversion 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.

SELECT CATEGORY_NAME
FROM TABLE_CATEGORY C, TABLE_PRODUCT P
WHERE
P.PRODUCT_NAME
IN('DTHVoucher Rs 500', 'E-RECHARGE', 'SMARTPHONEV6700')
AND P.PRODUCT2CATEGORY = C.ID;

CATEGORY_NAME
--------------------
E RECHARGE
HANDSET
PREPAID VOUCHERS

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 is

SELECT
DECODE(P.PRODUCT_NAME,'E-RECHARGE',C.CATEGORY_NAME) CATEGORY1,
DECODE(P.PRODUCT_NAME,'SMARTPHONEV6700',C.CATEGORY_NAME) CATEGORY2,
DECODE(P.PRODUCT_NAME,'DTHVoucher Rs 500',C.CATEGORY_NAME) CATEGORY3
FROM
TABLE_CATEGORY C,
TABLE_PRODUCT P
WHERE
P.PRODUCT_NAME IN ('DTHVoucher Rs 500', 'E-RECHARGE','SMARTPHONEV6700')
AND P.PRODUCT2CATEGORY = C.ID;

After executing this query, we get the below output

Step 2:
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. Modified query using MAX function is

SELECT
MAX(DECODE(P.PRODUCT_NAME,'E-RECHARGE',C.CATEGORY_NAME)) CATEGORY1,
MAX(DECODE(P.PRODUCT_NAME,'SMARTPHONEV6700',C.CATEGORY_NAME)) CATEGORY2,
MAX(DECODE(P.PRODUCT_NAME,'DTHVoucher Rs 500',C.CATEGORY_NAME)) CATEGORY3
FROM
TABLE_CATEGORY C,
TABLE_PRODUCT P
WHERE
P.PRODUCT_NAME IN ('DTHVoucher Rs 500', 'E-RECHARGE','SMARTPHONEV6700')
AND P.PRODUCT2CATEGORY = C.ID;

Output of the above query is

This is what we want to achieve.