Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transposing data (or how do I turn rows into columns)

Tips and Tricks

Transposing data (or how do I turn rows into columns)

by  taupirho  Posted    (Edited  )
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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top