Re: [GENERAL] Alias name from subquery

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