I think you should look at crosstab contrib module.
Regards,
Taras Kopets
On 9/30/08, Felix Homann <fexpop@onlinehome.de> wrote:
> Hi Sam,
>
> Sam Mason wrote:
>> I think you may be trying to solve the wrong problem, what are you
>> really trying to do?
>
> Here you go. I have some tables created like this:
>
> CREATE TABLE player(
> id INTEGER PRIMARY KEY,
> name varchar(20)
> );
>
> CREATE TABLE transfer(
> id SERIAL PRIMARY KEY,
> fromID INTEGER REFERENCES player(id),
> toID INTEGER REFERENCES player(id),
> amount numeric
> );
>
> Now, let's fill them with some data:
>
> INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
> INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
> '3', '1'), ('2','1','60');
>
> I would now like to have something like a view that transforms the table
> "transfer" from this:
>
> test=# SELECT * from transfer;
> id | fromid | toid | amount
> ----+--------+------+--------
> 1 | 1 | 2 | 3
> 2 | 1 | 3 | 1
> 3 | 2 | 1 | 60
>
>
> into this:
>
> id | Peter | David | Fritz | ...even more Names from player table
> ---+-------+-------+-------+-------------------------------------
> 1 | -3 | 3 | 0 | 0
> 2 | -1 | 0 | 1 | 0
> 3 | 60 | -60 | 0 | 0
>
>
> In other words, I would like to have a named column for every Name in
> the player table. I _can_ create such a view manually if I know each
> player.name beforehand, but I don't know how to automate it for any
> given number of players. (Think of something like a sparse interaction
> matrix representation.)
>
> Maybe it's the "wrong" problem I'm trying to solve, but at least I would
> like to know whether it's possible or not.
>
> Kind regards,
>
> Felix
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent from Gmail for mobile | mobile.google.com
