-
Download data into
Excel Pivot tables
-
The Oracle 9i xmlagg function
Let's look at each method to
consolidate data from multiple table rows onto a single row.
Download data into
Excel spreadsheet pivot table
Excel spreadsheets are a great
way to pivot and analyze Oracle data. Using Excel pivot tables with Oracle
data is a fast, easy way to use Oracle business intelligence without buying
expensive OLAP solutions (Hyperion, Oracle BI Suite).
Here is an example.
Oracle 9i xmlagg
In Oracle 9i and beyond 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
11g within group SQL
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
Write a PL/SQL
function
You can write a PL/SQL function to display
multiple rows values on a single line.
Martin Chadderton has written a Pl/SQL function called "stragg" that you
can define to display multiple SQL rows on one single line. Once defined,
you embed the function within your SQL to display your output on a single
line:
select stragg(ename) from emp;
Smith, Jones, Baker
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
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
)
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';
SQL Pivot OperatorThe 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
Oracle SQL CASE statement
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:
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
My Notes:
For more SQL
tips and tricks, see the book "Easy
Oracle SQL". Also see these
related notes on SQL output display consolidation: