Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    30

    Unanswered: Oracle SQL : Pivot Columns to Rows

    Hi, can someone please help me with some example SQL to run a select statement to pivot a table containing many repeating columns into rows, without doing multiple parses over the database ?

    Eg, I have a table with the following columns :

    A_Time, A_User, A_Amount, B_Time, B_User, B_Amount, C_Time, C_User, C_Amount
    ================================================== ====
    11:00, Bob, $1, 12:00, Bill, $2, 1:00, Ben, $3

    I want to run a query that displays it as

    Code, Time, User, Amount
    ====================
    A, 11:00, Bob, $1
    B, 12:00, Bill, $2
    C, 1:00, Ben, $3

    Hardcoding column names ( and codes for the output ), is absolutely fine by me, and probably necessary anyway... Thanks, Adam

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    All you need to do is to do a cartesian join to a table from which you select the same number of rows as there are users.

    i.e.

    select
    decode(i,1,'A',2,'B',3,'C', ...) Code,
    decode(i,1,A_Time,2,B_Time,3,C_Time, ...) Time,
    ...
    from x, -- your table
    (select rownum i from y where rownum<=z) y -- where z is the number of users

    Of course it would be much better for someone to normalise the table : )

    Alan

  3. #3
    Join Date
    Aug 2001
    Posts
    66
    Alternatively you could use a nested table to achieve this...
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> CREATE TABLE table_name (
      2    a_time DATE,
      3    a_user VARCHAR2 (30),
      4    a_amount NUMBER (5, 2),
      5    b_time DATE,
      6    b_user VARCHAR2 (30),
      7    b_amount NUMBER (5, 2),
      8    c_time DATE,
      9    c_user VARCHAR2 (30),
     10    c_amount NUMBER (5, 2));
    
    Table created.
    
    SQL> INSERT INTO table_name VALUES (
      2    TO_DATE ('11:00', 'HH24:MI'), 'Bob', 1, 
      3    TO_DATE ('12:00', 'HH24:MI'), 'Bill', 2, 
      4    TO_DATE ('13:00', 'HH24:MI'), 'Ben', 3);
    
    1 row created.
    
    SQL> CREATE TYPE type_name AS OBJECT (
      2    code VARCHAR2 (1),
      3    time DATE,
      4    username VARCHAR2 (30),
      5    amount NUMBER (5, 2));
      6  /
    
    Type created.
    
    SQL> CREATE TYPE nested_table_name AS TABLE OF type_name;
      2  /
    
    Type created.
    
    SQL> COLUMN CODE FORMAT A4;
    SQL> COLUMN TIME FORMAT A5;
    SQL> COLUMN AMOUNT FORMAT A6;
    SQL> COLUMN USERNAME FORMAT A8;
    SQL> 
    SQL> SELECT code, TO_CHAR (time, 'HH:MI') time, 
      2         username, TO_CHAR (amount, 'FM$99') amount
      3  FROM   table_name, 
      4         TABLE (
      5           nested_table_name (
      6             type_name ('A', a_time, a_user, a_amount), 
      7             type_name ('B', b_time, b_user, b_amount),
      8             type_name ('C', c_time, c_user, c_amount)));
    
    CODE TIME  USERNAME AMOUNT
    ---- ----- -------- ------
    A    11:00 Bob      $1
    B    12:00 Bill     $2
    C    01:00 Ben      $3
     
    SQL>
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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