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.