Create view to append instances to a single field 1

[Andyfives]

Hi there,

I have tried to find the answer to what I want to do in various documentation, but when I can not explain it myself very well without an example, I can not search for the solution so easily :-(

With an example maybe I can explain it successfully.

I have the following table:-

id | origin | destination
===========================
1 | LGW | CPH
2 | CPH | LGW
3 | LGW | ARN
4 | ARN | LGW
5 | LGW | OSL
6 | OSL | LGW
7 | CPH | TXL
8 | TXL | CPH
9 | LGW | TXL
10 | TXL | LGW


I would like to group on the origin and then create a new field that appends all entries of destination that equal the origin.

origin | destinations
===========================
LGW | CPH, ARN, OSL, TXL
CPH | LGW, TXL
ARN | LGW
OSL | LGW
TXL | CPH, LGW

Can anybody please help...

Andy

 

[feherke]

Hi

You have to use an aggregate function for that :

[b]select[/b] origin,sum(destination||[i]', '[/i]) [b]from[/b] andyfives [b]group[/b] [b]by[/b] origin;

While there is no such aggregate function by default, you have to define it before the first use :

[b]create[/b] [b]aggregate[/b] sum ( [b]basetype[/b]=text, [b]sfunc[/b]=textcat, [b]stype[/b]=text );

Feherke.

http://rootshell.be/~feherke/

 

[Andyfives]

Hi Feherke,

That is really great! Thank you