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(at)mascari(dot)com