On Mon, Apr 27, 2009 at 4:09 PM, Bryan Emrys
<bryan.emrys@gmail.com> wrote:
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.)
select State,sum(Dept1),sum(Dept2),sum(Dept3),sum(Dept4) from yourtable group by State;
Sean