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.

  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

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.

SQL> SELECT CATEGORY_NAME
  2  FROM TABLE_CATEGORY C, TABLE_PRODUCT P
  3  WHERE
  4  P.PRODUCT_NAME
  5  IN('DTHVoucher Rs 500', 'E-RECHARGE', 'SMARTPHONEV6700')
  6  AND P.PRODUCT2CATEGORY = C.ID;

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

SQL>

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.

SQL> SELECT
  2  DECODE(P.PRODUCT_NAME,'E-RECHARGE',C.CATEGORY_NAME) CATEGORY1,
  3  DECODE(P.PRODUCT_NAME,'SMARTPHONEV6700',C.CATEGORY_NAME) CATEGORY2,
  4  DECODE(P.PRODUCT_NAME,'DTHVoucher Rs 500',C.CATEGORY_NAME) CATEGORY3
  5  FROM
  6  TABLE_CATEGORY C,
  7  TABLE_PRODUCT P
  8  WHERE
  9  P.PRODUCT_NAME IN ('DTHVoucher Rs 500', 'E-RECHARGE','SMARTPHONEV6700')
 10  AND P.PRODUCT2CATEGORY = C.ID;

CATEGORY1       CATEGORY2       CATEGORY3
--------------- --------------- --------------------
E RECHARGE
                HANDSET
                                PREPAID VOUCHERS

SQL>

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. So below is the modified query using MAX function with result.

SQL> SELECT
  2  MAX(DECODE(P.PRODUCT_NAME,'E-RECHARGE',C.CATEGORY_NAME)) CATEGORY1,
  3  MAX(DECODE(P.PRODUCT_NAME,'SMARTPHONEV6700',C.CATEGORY_NAME)) CATEGORY2,
  4  MAX(DECODE(P.PRODUCT_NAME,'DTHVoucher Rs 500',C.CATEGORY_NAME)) CATEGORY3
  5  FROM
  6  TABLE_CATEGORY C,
  7  TABLE_PRODUCT P
  8  WHERE
  9  P.PRODUCT_NAME IN ('DTHVoucher Rs 500', 'E-RECHARGE','SMARTPHONEV6700')
 10  AND P.PRODUCT2CATEGORY = C.ID;

CATEGORY1       CATEGORY2       CATEGORY3
--------------- --------------- --------------------
E RECHARGE      HANDSET         PREPAID VOUCHERS

SQL>

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.

SQL> SELECT *
  2  FROM
  3    (SELECT C.CATEGORY_NAME,
  4           P.PRODUCT_NAME
  5     FROM TABLE_CATEGORY C, TABLE_PRODUCT P
  6     WHERE P.PRODUCT_NAME IN ('E-RECHARGE','SMARTPHONEV6700','DTHVoucher Rs 500')
  7     AND P.PRODUCT2CATEGORY = C.ID
  8    )
  9  PIVOT
 10     (
 11             MAX(CATEGORY_NAME)
 12             FOR PRODUCT_NAME IN ( 'E-RECHARGE','SMARTPHONEV6700','DTHVoucher Rs 500')
 13     );

'E-RECHARGE'         'SMARTPHONEV6700'      'DTHVoucher Rs 500'
-------------------- ---------------------- ----------------------
E RECHARGE            HANDSET                PREPAID VOUCHERS

SQL>

3. Convert rows into columns using CASE Operator:

Row to Column conversion using CASE operator is working same as explained in DECODE transposition. Below query gives us a desired output.

SQL> SELECT
  2  MAX(CASE WHEN P.PRODUCT_NAME = 'E-RECHARGE' THEN C.CATEGORY_NAME END) CATEGORY1,
  3  MAX(CASE WHEN P.PRODUCT_NAME = 'FWPCLASSIC25' THEN C.CATEGORY_NAME END) CATEGORY2,
  4  MAX(CASE WHEN P.PRODUCT_NAME = 'SAMSUNGB339' THEN C.CATEGORY_NAME END) CATEGORY3
  5  FROM
  6  TABLE_CATEGORY C,
  7  TABLE_PRODUCT P
  8  WHERE
  9  P.PRODUCT2CATEGORY = C.ID ;

CATEGORY1       CATEGORY2       CATEGORY3
--------------- --------------- --------------------
E RECHARGE      ACCESSORY ITEMS HANDSET

4. Convert rows into columns using XMLAGG Function:

By using XMLAGG Function we can convert set of rows into comma delimited single rows as follow.

SQL> SELECT
  2  C.CATEGORY_NAME,
  3  RTRIM(XMLAGG(XMLELEMENT(X,PRODUCT_NAME || ',')).EXTRACT('//text()'),',') PRODUCT_NAMES
  4  FROM TABLE_PRODUCT P,
  5  TABLE_CATEGORY C
  6  WHERE C.ID = P.PRODUCT2CATEGORY
  7  GROUP BY CATEGORY_NAME;

CATEGORY_NAME                  PRODUCT_NAMES
------------------------------ ---------------------------------------------------------------------------------------
ACCESSORY ITEMS                FWP- CLASSIC-23,FWPZTE 2208 UPFRONT-unpro,FWPHUAWEIF203BLACK,FWPCLASSIC25,FWPCLASSIC24
DATA CARDS/MODEMS              HSDUSBMODEM2737,Data Card-ZTE MC315 UNPRO,MODHSDZTEAC2738,MODEMHSDZTEA2736,HUWAEI2828
E RECHARGE                     E-RECHARGE,E-RECHARGE-GSM
HANDSET                        SMARTPHONEV6700,SAMSUNGB339,SAMSUNGB209,ZTES165,ZTES161
PREPAID VOUCHERS               DTHVoucher Rs 800,DTHVoucher Rs 300,DTHVoucher Rs 500,DTHWK Rs.2490,DTHVoucher Rs 2000

SQL>

5. Convert rows into columns using LISTAGG FUNCTION:

We can convert set of rows into a single comma delimited rows by using below query.

SQL> SELECT C.CATEGORY_NAME,
  2  LISTAGG(P.PRODUCT_NAME, ',') WITHIN GROUP (ORDER BY PRODUCT_NAME) PRODUCT_NAMES
  3  FROM TABLE_PRODUCT P,
  4  TABLE_CATEGORY C
  5  WHERE C.ID = P.PRODUCT2CATEGORY
  6  GROUP BY CATEGORY_NAME;

CATEGORY_NAME                  PRODUCT_NAMES
------------------------------ ----------------------------------------------------------------------------------------
ACCESSORY ITEMS                FWP- CLASSIC-23,FWPCLASSIC24,FWPCLASSIC25,FWPHUAWEIF203BLACK,FWPZTE 2208 UPFRONT-unpro
DATA CARDS/MODEMS              Data Card-ZTE MC315 UNPRO,HSDUSBMODEM2737,HUWAEI2828,MODEMHSDZTEA2736,MODHSDZTEAC2738
E RECHARGE                     E-RECHARGE,E-RECHARGE-GSM
HANDSET                        SAMSUNGB209,SAMSUNGB339,SMARTPHONEV6700,ZTES161,ZTES165
PREPAID VOUCHERS               DTHVoucher Rs 2000,DTHVoucher Rs 300,DTHVoucher Rs 500,DTHVoucher Rs 800,DTHWK Rs.2490

SQL>
SQL>
How to Convert rows into columns using SQL in Oracle

One thought on “How to Convert rows into columns using SQL in Oracle

  • November 10, 2015 at 12:23 pm
    Permalink

    Thanks for sharing such a wonderful post.

    Reply

Leave a Reply