Thursday, April 24, 2008

hierarchical queries

Here is an alternate way to get a comma (or any other character) separated list of values that comes from rows in a table. This solution is based on hierarchical queries.

Enjoy,
Danny


QUERY SAMPLE


with data
as
(
select myvalues, row_number() over (order by myvalues) rn, count(*) over () cnt
from
(
select VC_EMAILADDESS myvalues from PERM_EXTRACTS.TBL_EMAIL_ADDRESSES
)
)

select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
INTO p_vc_to
from data
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;




RESULT

P_VC_TO = dannnie.moodie@XXXXX.net,dannnie.moodie@XXXXX.com




SOURCE

http://www.quest-pipelines.com/pipelines/plsql/tips.htm#JULY

No comments: