From: | Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Question on collapsing a sparse matrix |
Date: | 2009-04-27 20:09:26 |
Message-ID: | eee4647d0904271309r6a9b0ff6s794f515479672535@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I've been handed a table that reminds me of a sparse matrix and I'm thinking
that there should be some SQL way to simplify it.
Assume table like (a column for every department, separate rows for each
state if any department has headcount in the state, but each row has only
one headcount entry):
State Dept1 Dept2 Dept3 Dept4
AZ 3 NULL NULL NULL
AZ NULL 2 NULL NULL
AZ NULL NULL 17 NULL
CA 2 NULL NULL NULL
CA NULL 21 NULL NULL
CA NULL NULL NULL 6
CA NULL NULL 4 NULL
etc
I'm trying to get to
State Dept1 Dept2 Dept3 Dept4
AZ 3 2 17 NULL
CA 2 21 4 6
etc
Is there some way of rolling up or ANDing records so that I can sum each
state into a single record per state? This looks like something that would
be obvious, but I'm apparently missing it. Any pointers would be
appreciated.
(BTW, there are a couple hundred departments in the actual table, they are
not conveniently numbered and as you may guess from the example, there is
not a consistent number of rows for each state; some have only 1 row, some
have 40 or more, it simply depends on how many departments have headcount in
that state.)
Bryan
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2009-04-27 20:15:53 | Re: Question on collapsing a sparse matrix |
Previous Message | Sean Davis | 2009-04-27 10:47:19 | Re: Copying data from one table of one database to other table f other database |