Re: sql statement: join

"Thomas Steffen" <thomas.rademacher_at_nikocity.de> wrote in message news:ba63dde5.0301081340.36aeb359_at_posting.google.com...
> I get the following results in cursor:
> (1) A_ID1, B_ID1
> (2) A_ID1, B_ID2
> (3) A_ID2, B_ID3
> (4) A_ID2, B_ID4
> (5) A_ID2, B_ID5
> (6) A_ID3, B_ID6
> ...
>
> Now I want to get the following results in cursor:
> (1) A_ID1, num1, B_ID1, B_ID2
> (2) A_ID2, num2, B_ID3, B_ID4, B_ID5
> (3) A_ID3, num3, B_ID6

You can't. The query return result set always contains the same number of columns.

The way around this limitation is treating your result set as a nested relation with 2 columns: A_ID, list of B_ID values. Here the second column is a collection. Therefore you just need a standard group by clause with non-standard aggregation:

select A_ID, nest(B_ID) from T
group by A_ID

where nest() aggregate function is not a standard sql, of course. Here you, therefore, pretty much on the mercy of your database vendor. For example, there are several different ways to write this aggregation in oracle. You might want to search google for "User Defined Aggregate Functions". Received on Thu Jan 09 2003 - 00:40:55 CET