Hi all,

I have a quite interesting question. Follow this please...

Structures:
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? I do not know how...
| id | name | flags |
|----|--------|---------------|
| 1 |pikeman | spear, armour |
| 2 |wolf | quick |


Very similar problem is how many flags has every unit:

SELECT unit.id, unit.name, a.flagCount FROM unit JOIN
(SELECT COUNT(flag.name) as flagCount, unit.id from flag join
unit_flag on flag.id = unit_flag.flagId
JOIN unit on unit.id = unit_flag.unitId GROUP BY unit.id) as a on a.id
= unit.id

| id | name | flagCount |
|----|--------|---------------|
| 1 |pikeman | 2 |
| 2 |wolf | 1 |


So I am thinking about write own aggregate function.
Is it possible to define own aggregate function (instead of COUNT
something like MERGESTRING) in the Firebird?