origins/destinations

Hi list,

I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:

I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
origins and destinations are the census area in which they and work.
What I would like to do is generate an nxn matrix (preferably output to
csv but I'll take what I can get), where origins are on the y axis, and
destinations on the x axis (see Table 3).

I can already group by both origins and destinations to produce Table 2,
but I don't know what steps are needed to get to Table 3. Any help or
suggestions are greatly appreciated!

Table 1

id | origin | destination
1 area1 area5
2 area1 area5
3 area1 area5
4 area2 area4
5 area4 area2
6 area5 area5
7 area2 area4
8 area2 area4
9 area4 area3
10 area3 area5
...

Table 2

id | origin | destination | count
1 area1 area5 3
4 area2 area4 3
5 area4 area2 1
6 area5 area5 1
9 area4 area3 1
10 area3 area5 1
...

Table 3

origins | area1 | area2 | area3 | area4 | area5 | ...
area1 0 0 0 0 3
area2 0 0 0 3 0
area3 0 0 0 0 1
area4 0 1 1 0 0
area5 0 0 0 0 1
...

Regards,

Carson

--
Carson J. Q. Farmer
ISSP Doctoral Fellow
National Centre for Geocomputation (NCG),
Email: Carson(dot)Farmer(at)gmail(dot)com
Web: http://www.carsonfarmer.com/
http://www.ftools.ca/