Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2001
    Posts
    21

    Unanswered: Oracle 9i: Pivot/Unpivot

    Hey all! Here is the situation that I am in. I have a table which is stored thusly:

    Code:
    create table t1 (
         Name1 varchar2(100),
         Address1 varchar2(100),
         Name2 varchar2(100),
         Address2 varchar2(100),
         .....
         Name50
         Address50
    )
    I want to create a select statement WITHOUT USING UNION OR UNION ALL (as this results in a query which takes more than 15 minutes to return!) which gives me:

    Code:
    Column_1_output         Column_2_output
    Name1                       Address1
    Name2                       Address2
    .....
    Name50                       Address50
    I cannot change the structure of the table (third-party vendor) and I cannot create a temp table or a materialized view (results of the query will be used for reporting, which has to be real-time).

    Help? Thanks!

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Search for pivoting at askTom Ask Tom Home
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Dec 2001
    Posts
    21
    Quote Originally Posted by asktom
    Sorry I have a large backlog right now, please ask a question later.


    (10characters)

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I didn't mean, you should ask Tom to send you the answer (probably most here could have told you).

    He would, btw, have told you the same I do:

    Pivoting examples require a lot of typing, and this question has been asked and answered with exhausting examples about ten million times in the last 15 years.

    So type at least the wort "pivoting" into the search line (especially if the link is set up in a way, that you only had to click the "search" button) before posting the same question again

    You will find at least 5 major threads explaining everything in detail.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    If this is going to be an ongoing process I would recommend placing each name/address into a separate row obviously in a separate table or into a nested table, anyway this is just a little quickie way to list in a column format, you will have to play around with the column spacing to get the spacing just right(i.e. lpad, rpad,etc):
    Code:
    declare
    
        cursor t1_cur is
        select name1, address1,,,,,,,,,,,name50, adresss50
         from  t1;
    
    begin 
      dbms_output.enable(100000);
      -- List names/addresses from each row
      for t1_rec in t1_cur
      loop
    
          dbms_output.put_line( 'Name......................................  Address..........................................' );
        dbms_output.put_line( name_rec.name1                             || name_rec.address1 );
        .....
        .....
        dbms_output.put_line( name_rec.name50                            || name_rec.address50 );
    
        end loop;
    
    dbms_output.put_line( '*******End of name/address list........' ); 
    
    end; 
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •