Andy Colson wrote:
Carson Farmer wrote:
Hi list,
I have (what I thought was) a relatively simple problem, but my
knowledge of sql is just not good enough to get this done:
I have a table which is basically a number of individuals with both
their origin and destination as columns (see Table 1). In this case,
origins and destinations are the census area in which they and work.
What I would like to do is generate an nxn matrix (preferably output
to csv but I'll take what I can get), where origins are on the y axis,
and destinations on the x axis (see Table 3).
<snip>
Would it have to be sql only? I think this would be pretty easy in perl.
-Andy
I took the liberty of assuming the origins and destinations could have
different values
Something like this:
#!/usr/bin/perl -w
use strict;
use DBI;
my $sql = 'select origin, dest, count(*) from tmp group by origin, dest';
my $db = DBI->connect('dbi:Pg:dbname=andy', 'andy', '') or die;
my $orlist = $db->selectcol_arrayref('select distinct origin from tmp
order by origin');
my $dstlist = $db->selectcol_arrayref('select distinct dest from tmp
order by dest');
my %table;
my $q = $db->prepare($sql);
$q->execute();
while (my($origin, $dest, $cc) = $q->fetchrow_array)
{
$table{$origin}->{$dest} += $cc;
}
print "origins\t";
foreach my $dst (@$dstlist)
{
print "$dst\t";
}
print "\n";
foreach my $ori (@$orlist)
{
print "$ori\t";
foreach my $dst (@$dstlist)
{
my $v = $table{$ori}->{$dst};
if (! $v) {
$v = '0';
}
print "$v\t";
}
print "\n";
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
