By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

Constructing column from different individual fields in same row.

P: n/a
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.
Nov 11 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <fr*********@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a
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
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #3

P: n/a
Bruno Wolff III wrote:
On Tue, Sep 09, 2003 at 21:02:21 -0700,
Alex Martinoff <fr*********@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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #4

P: n/a
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
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.