Cách truy vấn SQL chuyển từ dòng thành cột trong Oracle

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.

SQL> 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
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
  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-ECHARGE','SMARTPHONEV6700')
 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
    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;


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 *
    FROM
      (SELECT C.CATEGORY_NAME,
             P.PRODUCT_NAME
       FROM TABLE_CATEGORY C, TABLE_PRODUCT P
       WHERE P.PRODUCT_NAME IN ('E-RECHARGE','SMARTPHONEV6700','DTHVoucher Rs 500')
       AND P.PRODUCT2CATEGORY = C.ID
      )
    PIVOT
      (
              MAX(CATEGORY_NAME)
              FOR PRODUCT_NAME IN ( 'E-RECHARGE','SMARTPHONEV6700','DTHVoucher Rs 500')
 );


'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
    MAX(CASE WHEN P.PRODUCT_NAME = 'E-RECHARGE' THEN C.CATEGORY_NAME END) CATEGORY1,
    MAX(CASE WHEN P.PRODUCT_NAME = 'FWPCLASSIC25' THEN C.CATEGORY_NAME END) CATEGORY2,
    MAX(CASE WHEN P.PRODUCT_NAME = 'SAMSUNGB339' THEN C.CATEGORY_NAME END) CATEGORY3
    FROM
    TABLE_CATEGORY C,
    TABLE_PRODUCT P
    WHERE
    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
    C.CATEGORY_NAME,
    RTRIM(XMLAGG(XMLELEMENT(X,PRODUCT_NAME || ',')).EXTRACT('//text()'),',') PRODUCT_NAMES
    FROM TABLE_PRODUCT P,
    TABLE_CATEGORY C
    WHERE C.ID = P.PRODUCT2CATEGORY
    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,
    LISTAGG(P.PRODUCT_NAME, ',') WITHIN GROUP (ORDER BY PRODUCT_NAME) PRODUCT_NAMES
    FROM TABLE_PRODUCT P,
    TABLE_CATEGORY C
    WHERE C.ID = P.PRODUCT2CATEGORY
    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> 

Hãy bình luận đầu tiên

Để lại một phản hồi

Thư điện tử của bạn sẽ không được hiện thị công khai.


*