Help with SQL!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help with SQL!

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Help with SQL!

    Hello. I know it's a simplest thing but I'm stuck.
    There are 5 variables from a same table: A, B, C, D and E, as below
    A
    -------
    0.5

    B
    -------
    1.2

    etc


    I want to bring each one of them back in separate rows, but as one variable (call it Level 2 for example) so the result of query will look like

    Level 2 Rate
    -------- --------
    A 0.5
    B 1.2
    C 0.4
    D 3.1
    E 1.0

    I'm working in Oracle 9.1, using SQLPlus. Any help will be welcome! Please!!

    Thanks in advance
    Fiona

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    select 'A' "Level 2", A "Rate" from my_table
      UNION ALL
    select 'B' "Level 2", B "Rate" from my_table
      UNION ALL
    etc

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    83
    That worked brilliantly - thank you!

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Another method:
    Code:
    09:57:52 SQL> desc my_table
     Name                   Null?    Type
     --------------------- -------- ----------------------------
     C1                            NUMBER(38)
     C2                            NUMBER(38)
     C3                            NUMBER(38)
     C4                            NUMBER(38)
    
    09:58:24 SQL> select * from my_table ;
    
            C1         C2         C3         C4
    ---------- ---------- ---------- ----------
             1          2          3          4
    
    
    select  decode(code,1,c1,2,c2,3,c3,4,c4)
    from my_table, (select 1 code from dual union all
                    select 2 code from dual union all
                    select 3 code from dual union all
                    select 4 code from dual  )
    /
    DECODE(CODE,1,C1,2,C2,3,C3,4,C4)
    --------------------------------
                                   1
                                   2
                                   3
                                   4
    
    09:59:32 SQL> spool off
    ~
    Tamil

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Tamil, yes! might be faster too.

Posting Permissions

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


Click Here to Expand Forum to Full Width