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!

Search for "pivot" on: http://asktom.oracle.com/pls/ask/f?p=4950:1:

.......I said

PHP Code:

---

SQL> desc my_table
 Name                               Null?    Type
---------------------------------- -------- --------------------------------------------
 TRANSACTIONITEMID                          NUMBER(38)
 TRANSACTIONID                              NUMBER(38)
 ITEMNAME                                   VARCHAR2(20)

SQL> select * 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.

SQL> select transactionid,
  2         max(decode(rn, 1, itemname)) FIRST,
  3         max(decode(rn, 2, itemname)) SECOND,
  4         max(decode(rn, 3, itemname)) THIRD,
  5         max(decode(rn, 4, itemname)) FOURTH,
  6        max(decode(rn, 5, itemname)) FIFTH
  7  from (select transactionid, itemname,
  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

what if row_number is greater than 5?

=====
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

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.