Constructing column from different individual fields in same row.

On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <froggle2003@yahoo.com> 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:

You can use CASE to do this.

Bruno Wolff III wrote:

On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <froggle2003@yahoo.com> 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:

You can use CASE to do this.

You can also use UNION:

SELECT col1 ...
UNION
SELECT col2 ...

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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