Re: origins/destinations

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).
>>
> 
>
> 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.