Re: Merge Multiple Rows Into Single Result

TurkBear <john.greco_at_dot.state.mn.us> wrote in message news:<gtiktu0kkuqe2p4uoj50993on6k59tpt3e_at_4ax.com>...

> 1: Stop cross-posting

What do you mean?  

> 2: HINT - look into subqueries ( sometimes called in-line views)

Doesn't work if you're number of elements is unknown, yes I could use several derived tables to do it but it will be limited to the number of derived tables.

or I could try something like this, though the limit would be depending on the number of decodes you put in. This would be more efficient than derived/inlines etc.

select a,max(decode(pos,1,b))||max(decode(pos,2,b))||max(decode(pos,3,b)) from (select a,b,sum(1) over (partition by a order by b) pos

        from test_table)
group by a

>
>
>
>
> nick_wakefield_at_hotmail.com (Niko) wrote:
>
> >A cursor and a loop would be ideal, however I need to do it in a query
> >not pl/sql. The sybase ASA List function would be cool but I don't
> >know the oracle equivalent, never needed to know until now.
> >
> >
> >Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DD3D77C.FD124E08_at_exesolutions.com>...
> >> Niko wrote:
> >>
> >> > Hi,
> >> >
> >> > Does anyone know a way to do the following, given a table like
> >> >
> >> > Field_A, Field_B
> >> > a first word
> >> > a second word
> >> > a third word
> >> >
> >> > then runa group by query on it to merge the contents of field_b
> >> > seperating with commas.
> >> >
> >> > select field_a, merge(field_b,',')
> >> > from table
> >> > group by a
> >> >
> >> > resulting in
> >> > Field_A New_String
> >> > a first word, second word, thrid word
> >>
> >> Given that you are the second person in as many days asking the same
> >> question I can only presume that this is a class assignment ... so you
> >> will need to figure it out yourself.
> >>
> >> Hint: It requires a cursor and a loop.
> >>
> >> Daniel Morgan
Received on Tue Nov 19 2002 - 23:23:14 CET