Re: [GENERAL] Alias name from subquery

[Felix Homann]

Thanks to Scott and Taras for pointing me to the crosstab functions. I 
only had a quick look but they seem very helpful!


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

Re: [GENERAL] Alias name from subquery

[Felix Homann]

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

Re: [GENERAL] Alias name from subquery

[Felix Homann]

Raymond O'Donnell wrote:

select entry from (select name from colnames where id = 1) as
entry_with_different_name;

...maybe?


Thanks Ray!

No, "entry_with_different_name" should be the result of "SELECT name 
FROM colnames WITH id=1".



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

Re: [GENERAL] Alias name from subquery

[Felix Homann]

Hi Jeffrey,

Thanks for your quick response!


Hoover, Jeffrey wrote:


select (SELECT name from colnames WHERE id=1) as entry from entries;



I think, I should have been a bit clearer in what I need:

I've got two tables, colnames and entries:

test=# SELECT * from colnames;
 id | name
+--
  1 | col1
(1 row)

test=# SELECT entry from entries;
 entry

 first
 second
 third
(3 rows)

I would like to get the column name "entry" replaced by an alias "col1", 
just like this:


test=# SELECT entry as col1 from entries;
  col1

 first
 second
 third
(3 rows)

_But_, I don't want to give the alias explicitely, instead it should be 
taken from a second table 'colnames', i.e. something like the line I 
sent in my initial mail. Any idea?


Thanks again,

Felix

BTW, here's what I get from your command:

test=# select (SELECT name from colnames WHERE id=1) as entry from entries;
 entry
---
 col1
 col1
 col1
(3 rows)




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Alias name from subquery

[Felix Homann]

Hi,

I would like to set an alias name for a column from a subquery, i.e. 
something like this:


SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ;

Obviously it doesn't work _this_ way, but is there _any_ way to do it?

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