6 Replies Latest reply: Nov 20, 2012 2:57 PM by 975290 RSS

    How to transpose more than 40 element vertical to horizontal

    975290
      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
          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
            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
              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
                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
                  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
                    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.