Hello,

here's 5 lines of an example table to preface my question

TABLE_1

FIELD_A     FIELD_B
C1             PN1
R1             PN1
X1             PN2
X2             PN1
R2             PN2


the FIELD_A and FIELD_BE are the names of two fields in TABLE_1
and the values below them are 5 records contained in TABLE_1.


I need to understand how to write a SELECT statement that
produces two records as follows:

RESULT_FIELD_1              RESULT_FIELD_2

C1 R1 X2                        PN1
X1 R2                             PN2

In other words the result set consists of
a list of the FIELD_A contents when the FIELD_B records are GROUPED BY FIELD_B.

In the above example C1 R1 X2 consists of the list of FIELD_A values where FIELD_B is = PN1
and X1 R2 is the list of FIELD_A values where FIELD_B is = PN2.

probably the SELECT statement should look something like

SELECT ........................    FROM TABLE_1   GROUP BY FIELD_B

my question is what do I put into  ............................      to make this happen?

Thank you

Oliver

Oliver,

<< my question is what do I put into  ............................      to
make this happen? >>

Actually, what you would need is something that DBISAM currently does not
have - a LIST aggregate function that concatenates strings as part of the
grouping operation.

We've got this on the list of DBISAM enhancements, so I will see if I can
squeeze it in for you in the next couple of weeks.

Unfortunately there isn't any way to use a DBISAM custom function to do this
either, so the only other option is to loop through the records yourself and
do the grouping/concatenation in code.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, thanks! Actually that is exactly what I was looking for but could not find in the documentation: a list aggregate function.
I had considered writing some code to loop through the records accumulating the info.

If you do find the time to write one, I would like to suggest the following probably very useful item, at least for me.

Could you provide the option to add a delimeter after each item. For example if the  aggregate is
C1 C2 C4 C5   I'd like to have the option of adding a comma(or any arbitrary string) after each string (except for the last item.) Thus  obtaining
C1, C2, C4, C5

Thanks very much.

oliver

oliver


>Could you provide the option to add a delimeter after each item. For example if the aggregate is
>C1 C2 C4 C5 I'd like to have the option of adding a comma(or any arbitrary string) after each string (except for the last item.) Thus obtaining
>C1, C2, C4, C5

If its like the function in ElevateDB it will have.

Roy Lambert [Team Elevate]

Oliver,

<< Could you provide the option to add a delimeter after each item. For
example if the  aggregate is C1 C2 C4 C5   I'd like to have the option of
adding a comma(or any arbitrary string) after each string (except for the
last item.) Thus  obtaining C1, C2, C4, C5 >>

Yep, as Roy indicated, that's exactly how it works in EDB.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Hello Tim, just wondering if this improvement might have been added and if I could give it a try before generate release.

Thanks


Oliver

--------------------------


Oliver,

<< Could you provide the option to add a delimeter after each item. For
example if the  aggregate is C1 C2 C4 C5   I'd like to have the option of
adding a comma(or any arbitrary string) after each string (except for the
last item.) Thus  obtaining C1, C2, C4, C5 >>

Yep, as Roy indicated, that's exactly how it works in EDB.

--
Tim Young
Elevate Software
www.elevatesoft.com

Oliver,

<< Hello Tim, just wondering if this improvement might have been added and
if I could give it a try before generate release. >>

Not yet, no.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, just curious, did this request end up being implemented in 4.28 ?

Thanks

Oliver


"Tim Young [Elevate Software]" wrote:

Oliver,

<< Hello Tim, just wondering if this improvement might have been added and
if I could give it a try before generate release. >>

Not yet, no.

--
Tim Young
Elevate Software
www.elevatesoft.com

Oliver,

<< Tim, just curious, did this request end up being implemented in 4.28 ? >>

No, but I'll see if I can sneak it in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim.

Any news on this functionality?

Petter

P=E5 Tue, 09 Jun 2009 20:39:38 +0200, skrev Tim Young [Elevate Software]=
 =

<timyoung@elevatesoft.com>:

> Oliver,
>
> << Tim, just curious, did this request end up being implemented in 4.2=
8  =

> ? >>
>
> No, but I'll see if I can sneak it in the next build.
>


-- =

Sendt med Operas revolusjonerende e-postprogram: http://www.opera.com/ma=
il/