vojtech.vild wrote:

>
> CREATE TABLE UNIT (id integer PRIMARY KEY, name varchar(64) UNIQUE);
> CREATE TABLE FLAG (id integer PRIMARY KEY, name varchar(64) UNIQUE);
> CREATE TABLE UNIT_FLAG (unitId integer REFERENCES unit, flagId integer
> REFERENCES flag, PRIMARY KEY (unitId, flagId));
>
> I want in one select show flags for every unit separeted with the
> colon. Is it possible?

Only in FB 2.1.

> | id | name | flags |
> |----|--------|---------------|
> | 1 |pikeman | spear, armour |
> | 2 |wolf | quick |

select unit.id, unit.name, list(flag.name)
from unit
join unit_flag on unit.id = unit_flag.unitId
join flag on flag.id = unit_flag.flagId
group by 1, 2

> Very similar problem is how many flags has every unit:

For any FB version:

select unit.id, unit.name, could(unit_flag.flagId)
from unit
join unit_flag on unit.id = unit_flag.unitId
group by 1, 2

> So I am thinking about write own aggregate function.
> Is it possible to define own aggregate function in Firebird?

Not, it's not possible.


Dmitry