# Re: Marterialised View Problem

From: Randolf Geist <mahrah_at_web.de>
Date: Thu, 28 May 2009 07:13:30 -0700 (PDT)

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
>
> 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 aggr
```
from (
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

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

Original text of this message