Hi ,
My DB 10g
my data like below ,
84A8E46E9366 20111008133638
84A8E46E9366 20111112000531
84A8E46E9366 20111212004432
84A8E46E9366 20120127173533
84A8E46E9366 20120226235444
i've already pivot it by
select
msid,
rtrim (xmlagg (xmlelement (e, fee || '|')).extract ('//text()'), '|') fee
from
aaa_bill
where msid='84A8E46E9366'
and fee is not null
group by
msid;
and get the result as below with just 2 column
84A8E46E9366 20111008133638|20111112000531|20111212004432|20120226235444|20120127173533
but i need the result to be on multiple column ,
so i've tried the following
select msid,REGEXP_SUBSTR ( fee
, '^[^|]*'
)
from
(
select
msid,
rtrim (xmlagg (xmlelement (e, fee || '|')).extract ('//text()'), '|') fee
from
aaa_bill
where msid='84A8E46E9366'
and fee is not null
group by
msid
)
;
but the result only the first string
84A8E46E9366 20111008133638
is ther any way to get thos on many columns each string on seprated column with automatic way because i have about 20000 distinct msid