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.
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:-
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
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 V220.127.116.11). The Oracle documentation on sys_connect_by_path should give a big clue as to why it works.
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.
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.