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

Re: Question on collapsing a sparse matrix

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


In response to

pgsql-novice by date

Next:From: Dickson S. GuedesDate: 2009-04-27 22:56:38
Subject: Re: [NOVICE] Workaround for bug #4608?
Previous:From: Sean DavisDate: 2009-04-27 20:15:53
Subject: Re: Question on collapsing a sparse matrix

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