Re: Marterialised View Problem
Date: Thu, 28 May 2009 07:13:30 -0700 (PDT)
Message-ID: <0acbc1e5-26d4-46b9-9c53-137b02bdb241_at_q16g2000yqg.googlegroups.com>
On May 27, 2:44 pm, Kevin S <Sear..._at_googlemail.com> wrote:
> David,
>
> Thank you very much for this. You have just opened up a whole new
> world to me.
>
> I shall go away and play with it. Not sure my project manager is going
> to be too please about that but whatever.
>
> KS
Kevin,
please note that you don't need necessarily the aggregate functions to perform such a string aggregation, although it's a neat feature. There are numerous plain SQL solution available.
A nice collection of those can be found here on the SQL snippets site: http://www.sqlsnippets.com/en/topic-11787.html
To get you started, here a variant using a hierarchical query based on above sample table:
create table item_comment(
item_id number, item_desc varchar2(40), commnt varchar2(40)
);
insert all
into item_comment
values(1,'A','C ALPHA')
into item_comment
values(1,'A','C BETA')
into item_comment
values(1,'A','C GAMMA')
into item_comment
values(2,'B','C DELTA')
into item_comment
values(3,'C','C EPSILON')
into item_comment
values(3,'C','C ZETA')
select * From dual;
commit;
select
item_id , item_desc , substr(sys_connect_by_path(commnt, ','), 2) as aggrfrom (
select
item_id , item_desc , row_number() over (partition by item_id order by commnt) row_no , commnt from item_comment
)
where
connect_by_isleaf = 1
start with
row_no = 1
connect by
prior row_no = row_no - 1 and prior item_id = item_id order siblings by row_no
;
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
Received on Thu May 28 2009 - 09:13:30 CDT