7 cách chuyển đổi từ hàng thành cột trong Oracle

Converting Oracle rows to columns. There are native SQL techniques to display multiple columns onto a single row:

1. Oracle 9i xmlagg 
 In Oracle 9i we can use the xmlagg function to aggregate multiple rows onto one column:
 select
    deptno,
    rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
 from
    emp
 group by
    deptno
 ;
 DEPTNO ENAMES                              
 
     10 CLARK,MILLER,KING                         20 SMITH,FORD,ADAMS,SCOTT,JONES             30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
 Ex: 
 select msisdn,rtrim (xmlagg (xmlelement (e, service_package || ',')).extract ('//text()'), ',') service_package
 from  SUB_VAS.SUB_VAS_1
 group by msisdn
 
2. Use 11g SQL pivot for single row output 
 The SQL pivot operator allows you to take multiple rows and display them on a single line.
 select *
 from
   (select fk_department
    from employee)
    pivot
     (count(fk_department)
       for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
 'INT'         'WEL'       'CEN'     'POL'                                                                          
 ----------    ----------   ----------  -------
   7            6            0          8    


3. Use SQL within group for moving rows onto one line and listagg to display multiple column values in a single column 
 In Oracle 11g, we have the within group SQL clause to pivot multiple rows onto a single row.  We also a have direct SQL mechanism for non first-normal form SQL display. This allows multiple table column values to be displayed in a single column, using the listagg built-in function :
 select
    deptno,
    listagg (ename, ',')
 WITHIN GROUP
 (ORDER BY ename) enames
 FROM
    emp
 GROUP BY
    deptno
 /
     DEPTNO ENAMES                                        
 
     10 CLARK,KING,MILLER                                   20 ADAMS,FORD,JONES,SCOTT,SMITH                   30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD  
 
4. Use the SYS_CONNECT_BY_PATH operator
 This article by Younes Naguib describes how to display multiple values from a single column in a single output row.  In his example, he displays multiple values of the last name column on a single row.  Note his use of the sys_connect_by_path and over operators: 
 select
    deptno,
    substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
 from
    (
    select
      lname,
      deptno,
      count(*) OVER ( partition by deptno ) cnt,
      ROW_NUMBER () OVER ( partition by deptno order by lname) seq
    from
      igribun.emp
    where
      deptno is not null)
 where
    seq=cnt
 start with
    seq=1
 connect by prior
    seq+1=seq
 and prior
    deptno=deptno;
 DEPTNO NAME_LIST
 1      Komers,Mokrel,Stenko
 2      Hung,Tong
 3      Hamer
 4      Mansur

5. Use a Cross join
 Matt contributed this handy SQL techniques to pivot one row of several columns into a single column with several row, using the Oracle Cross join syntax.  Matt notes that the Cross join "has other uses in conjunction with a WHERE clause to create triangular result sets for rolling totals etc (though thanks to analytic functions those things are very nice and easy)". 
 SELECT
   ite,
   case
     when ite = 'item1' then item1
     when ite = 'item2' then item2
     when ite = 'item3' then item3
   end as val
 FROM
 (
   SELECT
     pivoter.ite,
     item1,
     item2,
     item3
   FROM
     someTable
     CROSS JOIN
     (
       select 'item1' as ite from dual
       UNION ALL
       select 'item2' as ite from dual
       UNION ALL
       select 'item3' as ite from dual
     )pivoter
 )

6. Use the Oracle analytic Lag-Over Function
 Analytic functions have a pronounced performance improvement since they avoid an expensive self-join and only make one full-table scan to get the results.  This site shows an example of using the Oracle LAG function to display multiple rows on a single column: 
 SELECT
    ename,
    hiredate,sal,LAG (sal, 1, 0)
    OVER (ORDER BY hiredate) AS PrevSal
 FROM
    emp
 WHERE
     job = 'CLERK';

7. Use the SQL CASE operator to pivot rows onto one line 
 You can use the CASE statement to create a crosstab to convert the rows to columns.  Below, the Oracle CASE function to create a "crosstab" of the results, such as this example from SearchOracle:
 
, sum(Sales.QtySold)   as Qty
  , sum(
      case when OH.MOHClass = 'Fixed'
           then OH.Amt
           else .00 end ) as MOHFixed
  , sum(
      case when OH.MOHClass = 'Var'
           then OH.Amt
           else .00 end ) as MOHVar
  , sum(
      case when OH.MOHClass = 'Cap'
           then OH.Amt
           else .00 end ) as MOHCap
 from Sales
 left outer
 join OH
 on Sales.ItemKey = OH.ItemKey
 group
 by Sales.ItemKey

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.


*