I know someone was just talking about this a day or so ago (or maybe
even today), but I can't seem to find it. So, maybe whoever it is that
wanted it will see this. This is Oracle-specific, but maybe you can
translate it. You can make a function like so that will do what you
want. I can't take much credit for this (other than figuring out that
it left an open cursor and closing it). I swiped it off the web
somewhere.
create or replace function join
(
p_cursor sys_refcursor,
p_del varchar2 := ','
) return varchar2
is
l_value varchar2(32767);
l_result varchar2(32767);
begin
loop
fetch p_cursor into l_value;
exit when p_cursor%notfound;
if l_result is not null then
l_result := l_result || p_del;
end if;
l_result := l_result || l_value;
end loop;
close p_cursor;
return l_result;
end join;
/
Then, you can call it in a query like so. (This joins 2 tables and
gets the corresponding ids from the secondary table.)
select sku, name, price, description, categoryid,
join(cursor(select categoryid from ces_pubs_overflow
where sku = ces_pubs.sku)) categoryList
from ces_pubs
order by sku
;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252881
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
