Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Oracle: All versions FAQ

Tips and Tricks

Transposing data (or how do I turn rows into columns) by taupirho
Posted: 15 Jul 10 (Edited 16 Jul 10)

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

---------- ----------
        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


substr(max(sys_connect_by_path(ename,',')),2) names
   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.





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.


  xmlagg (xmlelement (c, ename || ',')
  order by ename).extract ('//text()' ) x
FROM scott.emp
GROUP BY deptno

---------- -------------------------------------------------

Oh well, I never said method 2 would be any more easy to understand or interpret than method 1.


Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close