I don't know whether a stored procedure with EXECUTE STATEMENT or some of the newer Firebird features (like recursive stuff) can help you out in a better way, but if you can settle for a maximum value, then it is possible to do something like:

SELECT m1.Y_AKSE, m1.FIELDVALUE, m2.FIELDVALUE, m3.FIELDVALUE...
FROM MyTable m1
LEFT JOIN MyTable m2 on m1.Y_AKSE = m2.Y_AKSE and m1.X_AKSE < m2.X_AKSE
and not exists(select null from MyTable m2X
where m2X.X_AKSE > m1.X_AKSE and m2X.X_AKSE < m2.X_AKSE)
LEFT JOIN MyTable m3 on m1.Y_AKSE = m3.Y_AKSE and m2.X_AKSE < m3.X_AKSE
and not exists(select null from MyTable m3X
where m3X.X_AKSE > m2.X_AKSE and m3X.X_AKSE < m3.X_AKSE)
...
WHERE NOT EXISTS((select null from MyTable m1X
where m1X.X_AKSE < m1.X_AKSE)

In your example, this would result in

46 1 4 <null> ...
48 2 5 <null> ...
50 3 6 <null> ...

I generally use IBO, and rather than doing things like this, I'd rather have a TIB_Query that contained the simple

SELECT m1.Y_AKSE, m1.X_AKSE, m1.FIELDVALUE
FROM MyTable m1

and use this to feed XLSQL components where I had already set up a PivotTable. Ramil has done some good work with his 'IBO and Excel' components, they're really simple to use and Excel is far better at creating pivot tables than Firebird or IBO (I think, there are lots of IBO possibilities I've never tried, so I may be wrong).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Michael Vilhelmsen
Sent: 25. april 2008 11:35
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL Question

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.

Regards
Michael