Wednesday, May 14, 2008

Oracle 10g: String Aggregation Technique 2

Example if we have rows of data like the above picture and we want to compress it like the bottom picture. One way of doing it..by using string aggregation.

How?

select ownerid,max(sys_connect_by_path(phoneno1, ',')) phoneno1,max(sys_connect_by_path(faxno, ', ')) faxno
from ( select ownerid,faxno, phoneno1,
row_number() over(partition by ownerid order by ownerid) rn
from gbl_address_dtl where ownertype= 6008 and ownerid = 30)
start with rn = 1
connect by prior rn = rn-1 and prior ownerid = ownerid
group by ownerid
order by ownerid

3 comments: