Andy Colson wrote:
Carson Farmer wrote:
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).
<snip>
Would it have to be sql only? I think this would be pretty easy in perl.
indeed, this would better be done outside the database. you're
generating a sparse table of N x N dimensions and likely only relatively
few elements populated, unless your population count greatly exceeds the
number of locations. I think I'd do a SQL query for
distinct(source,dest),count(population) and then use this to fill your
matrix on the client side.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
