If I had a penny for every time some Oracle programmer wanted to know how to turn his query result set of rows into an equivalent set of columns, well, let's just say I'd be well on my way to my first pound by now.
Let's start with an example of what I'm talking about. We'll use the tried and trusted emp table of the scott schema as our data source.
Code:
select deptno,ename from scott.emp
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
Given this output, what a lot of users would really like to see is a grouping of all the names within each department with each grouping shown on a separate record, like this:-
DEPTNO NAMES
---------- -------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
In other words we want to transpose or pivot the row data into column data. Now, there are numerous ways of doing this but most involve some element of coding and those that don't, rely on you knowing in advance about the maximum number of columns of data in your desired output. What would be quite handy is for us to be able to do the transposition just using SQL and without knowing in advance the number of columns required. In fact, since Oracle introduced the sys_connect_by_path and hierarchical query constructs it has indeed been possible to write pivot-like queries just using pure SQL.
Here is SQL code for method 1
Code:
select
deptno,
substr(max(sys_connect_by_path(ename,',')),2) names
from
(
select deptno,ename,row_number()
over (partition by deptno order by ename) rn
from scott.emp
)
start with rn=1
connect by prior rn = rn -1
and prior deptno = deptno
group by deptno
order by deptno
I leave it to the reader to deconstruct this statement but suffice it to say it does indeed work (tested under V9.2.0.2). The Oracle documentation on sys_connect_by_path should give a big clue as to why it works.
i.e
SYS_CONNECT_BY_PATH Syntax
sys_connect_by_path(column,char)
Purpose
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.
If you happen to have Oracle XML there is another even lesser well known way than method 1 to get the same result as above. Again, with no extra coding required.
Here is the method 2 SQL.
Code:
SELECT
deptno,
xmlagg (xmlelement (c, ename || ',')
order by ename).extract ('//text()' ) x
FROM scott.emp
GROUP BY deptno
DEPTNO X
---------- -------------------------------------------------
10 CLARK,KING,MILLER,
20 ADAMS,FORD,JONES,SCOTT,SMITH,
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,
Oh well, I never said method 2 would be any more easy to understand or interpret than method 1.
Cheers.
