Oracle 10g: String Aggregation Technique 3

Another trick of doing data compression. Last time I did this was in the open source environment. Combining data using javascript and for loop to get the compressed data result. This technique is a little bit different, since I am mapping the DocumentNo with its Documenttype. Later then...the data will loop again to be binded to its Civilantid and produce only one row of data, instead of multiple rows.

How?

select civilantid,max(sys_connect_by_path(combinedoc, ' ')) documentno
from ( select civilantid,civdoc, documentno, (documenttype ', ' documentno) combinedoc,
row_number() over(partition by civilantid order by civdoc) rn
from gbl_civdoc_dtl where civilantid = 17)
start with rn = 1
connect by prior rn = rn-1 and prior civilantid = civilantid
group by civilantid
order by civilantid

To manipulate the given data, I did use the Split function in PHP...emm that was like 2 years ago..:)

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