--- In firebird-support@yahoogroups.com, "Fidel Viegas"
<fidel.viegas@...> wrote:

>
> On Fri, Apr 25, 2008 at 10:35 AM, Michael Vilhelmsen
> <Michael.Vilhelmsen@...> wrote:
>
> > Hi
> >
> > I have a table.
> > It contains data like:
> >
> > Y_AKSE X_AKSE FIELDVALUE
> > -------------------------
> > 46 BLACK 1
> > 48 BLACK 2
> > 50 BLACK 3
> > 46 WHITE 4
> > 48 WHITE 5
> > 50 WHITE 6
> >
> > I would like to do a SELECT statement, that returns something like:
> >
> > 46 1 4
> > 48 2 5
> > 50 3 6
> >
> > Which is the numbers represented by BLACK and WHITE.
> > Im not sure it can be done, but if it can it will help a lot.
> >
> > The problem is, that Y_AKSE can have a lot of distinct values as can
> > X_AKSE.
> > This example is just a short simple on.
>
> Hi Michael,
>
> You can try this:
>
> select distinct t.y_akse, t1.fieldvalue, t2.fieldvalue
> from tablename t
> join tablename t1
> on t.y_akse = t1.y_akse
> join tablename t2
> on t.y_akse = t2.y_akse
> where t1.x_akse = 'BLACK' and t2.x_akse = 'WHITE';
>
> tablename is the name of your table, since you have not provided one i
> just called it tablename.
>
> This returns your desired output, but maybe someone may come up with a
> more efficient way of achieving this.
>
> All the best,

This works for the specific example.

But I have simplified it a lot.
The X_Akse can have many values (more than just BLACK and WHITE).
In practice it contains some 10-20 diff. values.

The same goes for the Y_Akse.
This can also contain more than 3 values.
In practice it contains some 60 - 200 values.



Thx anyway

MIchael

>
> Fidel.
>