Skip site navigation (1) Skip section navigation (2)

Question on collapsing a sparse matrix

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2009-04-27 20:15:53
Subject: Re: Question on collapsing a sparse matrix
Previous:From: Sean DavisDate: 2009-04-27 10:47:19
Subject: Re: Copying data from one table of one database to other table f other database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group