Oracle SQL: Transpose / Convert Rows to Columns

There are native SQL techniques to display multiple columns onto a single row

• 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   

• 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      

• 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     

• 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

• 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
)

• 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 PrevSalFROM  
   emp
WHERE
    job = 'CLERK';

• 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 

select Sales.ItemKey
     , 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

Use a user-defined rowtocol function to convert the rows to columns:

CREATE OR REPLACE  FUNCTION 
   rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2

    AUTHID CURRENT_USER AS

 TYPE c_refcur IS REF CURSOR;

    lc_str VARCHAR2(4000);
    lc_colval VARCHAR2(4000);
    c_dummy c_refcur;
    l number;

    BEGIN

    OPEN c_dummy FOR p_slct;

    LOOP
    FETCH c_dummy INTO lc_colval;

    EXIT WHEN c_dummy%NOTFOUND;

    lc_str := lc_str || p_dlmtr || lc_colval;

    END LOOP;

    CLOSE c_dummy;

    RETURN SUBSTR(lc_str,2);

    /*
    EXCEPTION
    WHEN OTHERS THEN
    lc_str := SQLERRM;
    IF c_dummy%ISOPEN THEN
    CLOSE c_dummy;
    END IF;
    RETURN lc_str;
    */
    END;
    /

SELECT DISTINCT
   a.job,
   rowtocol(
    'SELECT ename
     FROM emp
     WHERE
     job = ' || '''' || a.job || '''' || ' ORDER BY ename')

     AS Employees