This discussion is archived
6 Replies Latest reply: Nov 20, 2012 12:57 PM by 975290 RSS

How to transpose more than 40 element vertical to horizontal

975290 Newbie
Currently Being Moderated
Hi friend!!

I have a problema with PLSQL, i need transpose more than 40 elements. I use this comand for only first 20 elements, but for more oracle have problems.

Another idea for this ¿?

declare
v_ide int_prod.cpo%type;
BEGIN
FOR someone IN ( select idetrans,
max( decode( cpo, 'F0001', valr, null ) )      F0001     ,
max( decode( cpo, 'F0002', valr, null ) )      F0002     ,
max( decode( cpo, 'F0003', valr, null ) )      F0003     ,
...
max( decode( cpo, 'F0023', valr, null ) )      F0023     ,
max( decode( cpo, 'F0024', valr, null ) )      F0024     ,
max( decode( cpo, 'F0025', valr, null ) )      F0025     
from ( select ide, cpo, valr
from int_datos_objfis
group by ide, cpo, valr )
group by ide)
LOOP
v_ide := someone.ide;
DBMS_OUTPUT.PUT_LINE('Name = ' || someone.ide);
END LOOP;
END;


Help me please to carlos@cardenas.pe
  • 1. Re: How to transpose more than 40 element vertical to horizontal
    ranit B Expert
    Currently Being Moderated
    This is called PIVOT in Oracle.

    Please refer -- http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

    Ranit B.
  • 2. Re: How to transpose more than 40 element vertical to horizontal
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the froum!
    972287 wrote:
    Hi friend!!

    I have a problema with PLSQL, i need transpose more than 40 elements. I use this comand for only first 20 elements, but for more oracle have problems.
    What exactly is the problem? If the code below does what you want for 20 columns (such as F0001, F0002, ....), it should work for any number of columns.
    Another idea for this ¿?
    If you're using Oracle 11, you could also user the SELECT ... PIVOT feature.
    declare
    v_ide int_prod.cpo%type;
    BEGIN
    FOR someone IN ( select idetrans,
    The only "table" in this query is the in-line view that returns 3 columns: ide (not ide<b>trans</b>), cpo and valr.
    If your code runs for any number of columns, then it's not what you posted.
    max( decode( cpo, 'F0001', valr, null ) )      F0001     ,
    max( decode( cpo, 'F0002', valr, null ) )      F0002     ,
    max( decode( cpo, 'F0003', valr, null ) )      F0003     ,
    ...
    max( decode( cpo, 'F0023', valr, null ) )      F0023     ,
    max( decode( cpo, 'F0024', valr, null ) )      F0024     ,
    max( decode( cpo, 'F0025', valr, null ) )      F0025     
    from ( select ide, cpo, valr
    from int_datos_objfis
    group by ide, cpo, valr )
    group by ide)
    LOOP
    v_ide := someone.ide;
    DBMS_OUTPUT.PUT_LINE('Name = ' || someone.ide);
    END LOOP;
    END;
    If you're not doing anyhting with the pivoted columns, why bother computing them? Everything above could just be replaced by:
    SELECT DISTINCT
            'Name = ' || ide
    FROM    int_datos_objfis
    ;
    Help me please to carlos@cardenas.pe
    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    If the problem involves an error message, post the complete error message, including line numbers.
    Always say which version of Oracle you're using (for example, 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    The forum FAQ also has a section on pivoting: {message:id=9360005}

    Edited by: Frank Kulash on Nov 20, 2012 1:06 PM
  • 3. Re: How to transpose more than 40 element vertical to horizontal
    975290 Newbie
    Currently Being Moderated
    Hello.

    I didnt used pivot because in oracle 9.2 this no exists :/
  • 4. Re: How to transpose more than 40 element vertical to horizontal
    975290 Newbie
    Currently Being Moderated
    Hello Frank Kulash

    I appreciate your help.

    by the way

    I didnt copy my original code only change some field for security :)

    Well this problem begin hear:
    I have a table and each row insert have a two element like cpo and valr. Cpo is a head field in other table (tba1) wich have a other name like 'F0001' is a 'PULLNAME' o similary and this table hace 60 field.

    My Oracle Version with select * from v$version is:

    Oracle9i Release 9.2.0.8.0 - 64bit Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE     9.2.0.8.0     Production

    My sentence with correction is:

    select ide,
    max( decode( cpo, 'F0001', valr, null ) ) F0001 ,
    max( decode( cpo, 'F0002', valr, null ) ) F0002 ,
    max( decode( cpo, 'F0003', valr, null ) ) F0003 ,
    ...
    max( decode( cpo, 'F0043', valr, null ) ) F0043 ,
    max( decode( cpo, 'F0044', valr, null ) ) F0044 ,
    max( decode( cpo, 'F0045', valr, null ) ) F0045
    from ( select ide, cpo, valr
    from int_datos_objfis
    group by ide, cpo, valr )
    group by ide)


    My Task is change the table orientation for insert the data into 'tba1'.

    The error is: ORA-01467: sort key too long

    Thank you for the links.

    NOTE: some comand are restricte like 'with' o 'rownum' .
  • 5. Re: How to transpose more than 40 element vertical to horizontal
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    972287 wrote:
    ... I didnt copy my original code only change some field for security :)
    Of course you shouldn't post anything private. Change whatever data, column names and table names you have to, and post CREATE TABLE and INSERT statements for the modified tables, as well as your query (or PL/SQL code) that references those modified tables and data. Post the results you want to get from the modified data, and explain how you get those results from the data you posted.
    ... My Oracle Version with select * from v$version is:

    Oracle9i Release 9.2.0.8.0 - 64bit Production ...
    That's very old; Oracle 10 was released in 2003. Think about upgrading. The SELECT ... PIVOT feature is just one reason why that will help you.
    ... NOTE: some comand are restricte like 'with' o 'rownum' .
    Sorry, I don't understand.
  • 6. Re: How to transpose more than 40 element vertical to horizontal
    975290 Newbie
    Currently Being Moderated
    Some commands are restricted :).

    Thanks for the observation. I used pivot in other time but now is not posible.

    Welll, i made a cursor and this no had problems, is posible to use that.


    Thank a lot.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points