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:
rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
DEPTNO ENAMES 10 CLARK,MILLER,KING 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
select msisdn,rtrim (xmlagg (xmlelement (e, service_package || ',')).extract ('//text()'), ',') service_package
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.
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 :
listagg (ename, ',')
(ORDER BY ename) 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:
substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
deptno is not null)
connect by prior
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)".
when ite = 'item1' then item1
when ite = 'item2' then item2
when ite = 'item3' then item3
end as val
select 'item1' as ite from dual
select 'item2' as ite from dual
select 'item3' as ite from dual
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:
hiredate,sal,LAG (sal, 1, 0)
OVER (ORDER BY hiredate) AS PrevSal
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
case when OH.MOHClass = 'Fixed'
else .00 end ) as MOHFixed
case when OH.MOHClass = 'Var'
else .00 end ) as MOHVar
case when OH.MOHClass = 'Cap'
else .00 end ) as MOHCap
on Sales.ItemKey = OH.ItemKey