Can we achieve the following requirement through a query?

[srinivasm]

Hi,

Can we achieve the following requirement through a query?

The requiement is as follows. There is a table which tracks the user crecitcard transactions.
An User can purchase "n" of items in one transaction. If an user purchase 3 items in one transaction
the table will be populated with 3 different rows. Each row will contain the transactionid and
the item name, and the transactionid of all the 3 rows are same(since all 3 items were purchased in a single transaction)

Table : transaction_details

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


Now I want a query which should return the result as follows

100 course1,course2,course3
101 course2,course5
110 course1,course2,course3

I need to combine all the itemNames with a comma of each transaction
and get that in one row for each transaction,
instead of "n" of rows for "n" of items in each transaction.

How we can achive this?


Thanks in advance

Srinivas Medukonduru

[DaPi]

You can base it on this - for a known max number of items:
http://asktom.oracle.com/pls/ask/f?p...:419593546543,

[tamilselvan]

Try this:

select transactionid, 
       max(decode(rn, 1, itemname)) FIRST,
       max(decode(rn, 2, itemname)) SECOND,
       max(decode(rn, 3, itemname)) THIRD,
       max(decode(rn, 4, itemname)) FOURTH,
	   max(decode(rn, 5, itemname)) FIFTH
from (select transactionid, itemname,
       row_number() over 
       (partition by transactionid 
        order by itemname ) RN 
         from my_table	)     
 where rn <= 5  
 group by transactionid;

Tamil