Transpose rows into columns

[dharma]

I have some data in a table with data in rows and would like to transform them to rows.

the data is like this

id no val
----------
01 10 123
01 20 100
01 30 234
02 10 125
02 20 345

and should be transformed into (and inserted into a table)

id |10 | 20 | 30
---------------------
01 |123| 100| 234
02 |125| 345| 0


is there any way to do that other than a

select a.id,a.no,a.val,b.val,c.val
from
(select id,no,val from table
where no=10) a,
(select id,no,val from table
where no=20) b,
(select id,no,val from table
where no=30) c
where a.id=b.id
and a.id=c.id

(also note that I may need something of a FULL OUTER JOIN)
DB Version : 9i rel 2

Any help is appreciated.
Thanks,
-dharma

[dharma]

mmm..a small push isnt a bad idea

PS: apoplogies though.