From: | David Gardner <david(at)gardnerit(dot)net> |
---|---|
To: | |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Question on collapsing a sparse matrix |
Date: | 2009-04-27 20:19:41 |
Message-ID: | 49F6135D.8040606@gardnerit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Take a look into the crosstab function in the tablefunc contrib module.
http://www.postgresql.org/docs/8.3/static/tablefunc.html
Might be able to do what you are describing.
Bryan Emrys 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.)
>
> Bryan
>
--
David Gardner
From | Date | Subject | |
---|---|---|---|
Next Message | Dickson S. Guedes | 2009-04-27 22:56:38 | Re: [NOVICE] Workaround for bug #4608? |
Previous Message | Sean Davis | 2009-04-27 20:15:53 | Re: Question on collapsing a sparse matrix |