4

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.

8
  • 1
    Would you like to actually tell us what you're trying to accomplish?
    – Jacobm001
    Aug 7, 2013 at 15:18
  • @Jacobm001 sorry about that! I have adited my question!
    – Rafey
    Aug 7, 2013 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/… Aug 7, 2013 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, 2013 at 15:35
  • 2
    @Rafeyiftikhar See this answer on executing the procedure
    – Taryn
    Aug 7, 2013 at 15:41

2 Answers 2

2

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;
/
4
  • 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, 2013 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, 2013 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, 2013 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, 2013 at 10:05
3

You are missing a semicolon:

VARIABLE x REFCURSOR  
BEGIN  
   dynamic_pivot_po(:x); -- here
END; -- and here
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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