Re: Marterialised View Problem
Date: Thu, 28 May 2009 07:13:30 -0700 (PDT)
On May 27, 2:44 pm, Kevin S <Sear..._at_googlemail.com> wrote:
> 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.
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)
select * From dual;
item_id , item_desc , substr(sys_connect_by_path(commnt, ','), 2) as aggrfrom (
item_id , item_desc , row_number() over (partition by item_id order by commnt) row_no , commnt from item_comment
connect_by_isleaf = 1
row_no = 1
prior row_no = row_no - 1 and prior item_id = item_id order siblings by row_no
Oracle related stuff blog: