Transform Multiple Rows To Multiple Columns In Oracle

[matrun]

Could anyone tell me how I would transform multiple rows into multiple columns in oracle, assuming that i did NOT know the total number of outgoing columns (i.e. it's flexible).
So turning a list like this:

NAME OCCUPATION
MATT BUILDER
JEFF BUILDER
GEORGE BUILDER
CHRIS PLUMBER
JOHN PLUMBER

...into:

OCCUPATION NAME1 NAME2 NAME3
BUILDER MATT JEFF GEORGE
PLUMBER CHRIS JOHN [NULL]

Any help much appreciated! Can this be done with a single SQL query?

Regards

Matt

 

[LKBrwnDBA]

In order for you to do your homework, we suggest you first search this forum for "rows to columns", "pivot table" or "crosstab table" and then try coding something yourself.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[matrun]

Hi - no, it's not 'homework', although i'm not sure what you mean; i'm 35, not a schoolkid!!
i've actually worked it out using decode but can't [with that] get this to base the number of resultant columns on the number of columns that would appear for the string with the highest number of delimiters (rather than just writing 100 decode statements just in case), so if you know a way, it would be much appreciated!
I've used this forum a few times to help others and ask questions but to this one and another question, just yesterday, i seem to get rather wary responses; is it something i said??

 

[SantaMufasa]

is it something i said??

As you are probably aware, Matrun, site policies proscribe us from assisting with homework. Your two posts yesterday simply looked rather "homeworky". But trusting your disclosure that this is not homework, here is a solution that should take care of business for you without the extreme complexity of multiple DECODE statements:

col Personnel format a30
select occupation,
       ltrim(substr(max(sys_connect_by_path(name,', ')),2)) Personnel
  from (select occupation, name,row_number() over (partition by occupation order by name) rn from person)
 start with rn=1
connect by prior rn = rn -1
       and prior occupation = occupation
 group by occupation
 order by occupation
/

OCCUPATION PERSONNEL
---------- -------------------
BUILDER    GEORGE, JEFF, MATT
PLUMBER    CHRIS, JOHN

Of course this solution does not provide a separate column for each worker, but I believe that you probably don't really want/need that...it complicates the output enormously.


Let us know if this resolves your need or if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

Or if you have Workspace manager installed (WMSYS), try this:

Select Occupation, Ws_Concat(Name) Personnel
  From Person
 Group By Occupation;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[matrun]

Hi - thanks for that; I already have that as a solution for comma-delimiting a list into one column; however the user's requirement was that it would go into separate columns, the maximum number of columns equating to the maximum number of delimited information (were that all in one column), in order to avoid excessive null columns.
This said, I think the request is silly as either way one could risk having many null columns even if the maximum number were known, so I'll probably knock it back.

Thanks for your help!

Matt

 

[CassidyHunt]

Looks like I double posted this same request in a different form. I am looking for the ability to do this too.

http://www.oracle.com/technology/oramag/code/tips2004/050304.html

Is the closest thing I have found to being able to accomplish it.

Anyone find a way to do this that will compile?

Cassidy

 

[SantaMufasa]

Anyone find a way to do this that will compile?

Did you run the code toward the bottom of Krishna's posting:

CREATE OR REPLACE FUNCTION rowtocol( p_slct IN VARCHAR2, p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2 
    AUTHID CURRENT_USER
    /*
    1) Column should be character type.
    2) If it is non-character type, column has to be converted into character type. 
    3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.
    4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument.
    */
AS
    TYPE c_refcur IS REF CURSOR;
    lc_str VARCHAR2(4000);
    lc_colval VARCHAR2(4000);
    c_dummy c_refcur;
    l number;
BEGIN
    OPEN c_dummy FOR p_slct;
    LOOP
        FETCH c_dummy INTO lc_colval;
        EXIT WHEN c_dummy%NOTFOUND;
        lc_str := lc_str || p_dlmtr || lc_colval;
    END LOOP;
    CLOSE c_dummy;
    RETURN SUBSTR(lc_str,2);
END;
/

Function created.

select id, name, rowtocol('select distinct last_name from s_emp where dept_id = '||a.id) as emps
from s_dept a;

ID NAME            EMPS
-- --------------- --------------------------
10 Finance         Quick-To-See
31 Sales           Magee,Nagayama
32 Sales           Giljum
33 Sales           Sedeghi
34 Sales           Nguyen,Patel
35 Sales           Dumas
41 Operations      Maduro,Ngao,Smith,Urguhart
42 Operations      Menchu,Nozaki,Patel
43 Operations      Biri,Markarian,Newman
44 Operations      Catchpole,Chang
45 Operations      Dancs,Havel,Schwartz
50 Administration  Ropeburn,Velasquez

The other method to achieve these results (without using a user-defined function) is to use the code I posted, above.


Let us know.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”