Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this table in the below format:

Persnbr | Userfieldcd | Value
01      | Port | Funds   
01      | Vip1 | Systems  
02      | Port | Bank  
02      | Vip1 | Authority   

This is how I want it:

Persnbr | Port  | Vip1
01      | Funds | Systems   
02      | Bank  | Authority

As I dont know the all the fields in the userfieldcd column, I am trying to dynamically pivot the table. So I am using this procedure but I dont know how to call it in PL/SQL developer. I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select persnbr ';

begin
    for x in (select distinct userfieldcd from persuserfield order by 1)
    loop
        sql_query := sql_query ||
            ' , min(case when userfieldcd = '''||x.userfieldcd||''' then value else null end) as '||x.userfieldcd;

            dbms_output.put_line(sql_query);
    end loop;

    sql_query := sql_query || ' from persuserfield group by persnbr order by persnbr';
    dbms_output.put_line(sql_query);

    open p_cursor for sql_query;
end;
/

When I call the procedure using:

VARIABLE x REFCURSOR  
BEGIN  
       dynamic_pivot_po(:x)  
    END  
    /

it gives me

ORA-00900: Invalid SQL statement.

share|improve this question
1  
Would you like to actually tell us what you're trying to accomplish? –  Jacobm001 Aug 7 '13 at 15:18
    
@Jacobm001 sorry about that! I have adited my question! –  Rafey Aug 7 '13 at 15:28
1  
Oracle 11 and higher has a built in 'PIVOT', see one of Tim Hall's excellent summaries: oracle-base.com/articles/11g/… –  Colin 't Hart Aug 7 '13 at 15:30
    
@Jacobm001 the reason I am trying to do this dynamically is because the fields in the "userfieldcd" column are not consistent and tend to change. –  Rafey Aug 7 '13 at 15:35
2  
@Rafeyiftikhar See this answer on executing the procedure –  bluefeet Aug 7 '13 at 15:41
show 3 more comments

2 Answers 2

up vote 1 down vote accepted

You can do it with PIVOT operator, but it require hardcoding all the pivoting values.

select * from (select Persnbr, Userfieldcd, value from pivot_test) pivot (max(value) for Userfieldcd in ('Port', 'Vip1'));

Building the query dynamically as your example:

declare
    in_clause varchar2(256);
    sel_query varchar2(256);
    n number := 0;
begin
    for x in (select distinct userfieldcd from persuserfield)
    loop    
        if n <> 0 then 
                in_clause := in_clause || ', ';
        end if;
        in_clause := in_clause ||  '''' || x.userfieldcd || '''';
        n := 1;    
    end loop;
    sel_query := 'select * from (select Persnbr, userfieldcd, value from persuserfield) pivot (max(value) for userfieldcd in ('||in_clause||'));';
    dbms_output.put_line (sel_query);
end;
/
share|improve this answer
    
the reason why I dont want to do this is because I dont know all the pivoting values and they are always being updated. –  Rafey Aug 7 '13 at 16:55
    
I've edited the answer to do a similar procedure as yours with PIVOT operator An alternative is to use operator PIVOT XML which allows you to use the wildcard ANY, but it will return a formatted XML result –  vegatripy Aug 8 '13 at 10:53
    
thank you for this!! but when I try to run this it gives me: ORA-01008: not all variables bound. Why do you think that is? –  Rafey Aug 8 '13 at 13:34
    
Check the code after you copy/paste/edited it. Check all variables. Maybe you are renaming a variable, but didn't rename it in every line. –  vegatripy Aug 9 '13 at 10:05
    
thanks a lot! You are the best!! –  Rafey Aug 9 '13 at 14:11
add comment

You are missing a semicolon:

VARIABLE x REFCURSOR  
BEGIN  
   dynamic_pivot_po(:x); -- here
END; -- and here
/
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.