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

Re: Alias name from subquery

From: Felix Homann <fexpop(at)onlinehome(dot)de>
To: Sam Mason <sam(at)samason(dot)me(dot)uk>, pgsql-general(at)postgresql(dot)org
Subject: Re: Alias name from subquery
Date: 2008-09-30 17:45:32
Message-ID: 48E265BC.5090206@onlinehome.de (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2008-09-30 17:53:59
Subject: Re: Alias name from subquery
Previous:From: Scott MarloweDate: 2008-09-30 17:39:51
Subject: Re: Can't cast from char to integer...

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