Re: Question on collapsing a sparse matrix

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