Обсуждение: Constructing column from different individual fields in same row.

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

Or, is it necessary to waste storage by making prefname varchar(30)
and then duplicating the preferred name into the prefname field?

Thanks for any answers.

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