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.Farmer@gmail.com
Web: http://www.carsonfarmer.com/
http://www.ftools.ca/