-
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!
-
-
.......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?
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
-
=====
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|