×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

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.


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.

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