Re: Constructing column from different individual fields

Alex Martinoff wrote:

> I'm wondering if it's possible to have a query construct a column
> where the value of the column at each row is taken from another field
> in that same row. For example, suppose you have a table like:
>
> create table users (
>     uid serial,
>     nickname varchar(20),
>     realname varchar(30),
>     prefname int2,
>     primary key (uid)
> );
>
> insert into users (nickname, realname, prefname)
> values ('Stevo', 'Steve Sullivan', 1);
>
> insert into users (nickname, realname, prefname)
> values ('Johnny Boy', 'John Fisk', 2);
>
>
> A prefname of 1 means the user prefers their nickname, while 2 means
> they prefer their realname.
>
> Is there a query I can perform that would return:
>
>  uid | Preferred Name
> -----+----------------
>    1 | Stevo
>    2 | John Fisk


SELECT uid, (CASE WHEN prefname = 1 THEN nickname ELSE realname END)
AS "Preferred Name"
FROM users
WHERE ...

HTH,

Mike Mascari
mascarm@mascari.com