DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: more values on one line

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    more values on one line

    Hi,
    I've these tables:

    tab1:
    ID
    1
    2
    3

    tab2:
    ID..........APPROV
    1............A1
    1............A2
    2............A22
    2............A34
    2............A44
    3............AAA

    I'd like to ge this output:
    ID...........APPROV
    1............A1, A2
    2............A22, A34, A44
    3............AAA


    How can I write my query to get this output?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    .......I said
    PHP Code:
    SQLdesc my_table
     Name                               Null
    ?    Type
    ---------------------------------- -------- --------------------------------------------
     
    TRANSACTIONITEMID                          NUMBER(38)
     
    TRANSACTIONID                              NUMBER(38)
     
    ITEMNAME                                   VARCHAR2(20)

    SQLselect from my_table ;

    TRANSACTIONITEMID TRANSACTIONID ITEMNAME
    ----------------- ------------- --------------------
                    
    1           100 course1
                    2           100 course2
                    3           100 course3
                    4           101 course2
                    5           101 course5
                    6           110 course1
                    7           110 course2
                    8           110 course3

    8 rows selected
    .

    SQLselect transactionid,
      
    2         max(decode(rn1itemname)) FIRST,
      
    3         max(decode(rn2itemname)) SECOND,
      
    4         max(decode(rn3itemname)) THIRD,
      
    5         max(decode(rn4itemname)) FOURTH,
      
    6        max(decode(rn5itemname)) FIFTH
      7  from 
    (select transactioniditemname,
      
    8         row_number() over (partition by transactionid order by itemname RN
      9           from my_table     
    )
     
    10   where rn <= 5
     11   group by transactionid
    ;

    TRANSACTIONID FIRST        SECOND          THIRD          FOURTH        FIFTH
    ------------- ------------ --------------- -------------- ------------- --------------------
              
    100 course1      course2          course3
              101 course2      course5
              110 course1      course2          course3

    SQL
    spool off 
    Tamil

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    what if row_number is greater than 5?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =====
    what if row_number is greater than 5?
    =========
    ........I said.....

    You must first know how many columns you want in the report before writing the actual code. Otherwise, the system is useless.

    Tamil

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Frankly, if I need something that looks like an Excel spread-sheet, I use Excel. Summerise & write a text file in Oracle, read & pivot with a macro in Excel. Unfortunately you're limited to 64k rows.

    If you must do it in Oracle and the number of columns is dynamic (e.g. one column per week) then you need some pretty mean PL/SQL to handle it.

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