I m new to PostgreSQL so please tell me the drawbacks of is this solution
Your Table 3 should not be a table it should be a array.
CREATE TABLE table3 ( ori_des int[][] );
for origin area_n and destination area_m if count is k, then ori_des[n][m] = k.
--- On Tue, 19/5/09, Carson Farmer <carson.farmer@gmail.com> wrote:
From: Carson Farmer <carson.farmer@gmail.com> Subject: [GENERAL] origins/destinations To: pgsql-general@postgresql.org Cc: "Carson Farmer" <Carson.Farmer@nuim.ie> Date: Tuesday, 19 May, 2009, 10:27 PM
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!
