Thread: Query to return modified results at runtime?

I have a query:

SELECT * FROM testtable;

Where the results are:

ID    Color
---   -------
1     Blue
2     Red
3     Green
4     Orange

How would I rewrite the query to return results where the colors are 
replaced by letters to give the following results?

ID    Color
---   -------
1     A
2     D
3     B
4     C

> ID    Color
> ---   -------
> 1     Blue
> 2     Red
> 3     Green
> 4     Orange
> 
> How would I rewrite the query to return results where the colors are 
> replaced by letters to give the following results?
> 
> ID    Color
> ---   -------
> 1     A
> 2     D
> 3     B
> 4     C


http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html

this is probably the easiest to implement but hard to mangage over time.  Another solution would
be to create color_code table that is referenced by your test table.  Then when you can create a
query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color;

There are additional solutions to this also. But these two are probably the easiest.

Richard Broersma Jr wrote:
>> ID    Color
>> ---   -------
>> 1     Blue
>> 2     Red
>> 3     Green
>> 4     Orange
>>
>> How would I rewrite the query to return results where the colors are 
>> replaced by letters to give the following results?
>>
>> ID    Color
>> ---   -------
>> 1     A
>> 2     D
>> 3     B
>> 4     C
> 
> 
> http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html
> 
> this is probably the easiest to implement but hard to mangage over time.  Another solution would
> be to create color_code table that is referenced by your test table.  Then when you can create a
> query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color;
> 
> There are additional solutions to this also. But these two are probably the easiest.

Thanks!

On Wed, 2006-06-07 at 19:29 -0500, George Handin wrote:
> I have a query:
> 
> SELECT * FROM testtable;
> 
> Where the results are:
> 
> ID    Color
> ---   -------
> 1     Blue
> 2     Red
> 3     Green
> 4     Orange
> 
> How would I rewrite the query to return results where the colors are 
> replaced by letters to give the following results?
> 
> ID    Color
> ---   -------
> 1     A
> 2     D
> 3     B
> 4     C
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

It looks like you may want to use a validation table:

Name: color_validate
ID    Color    Code
---    ---    ---
1    Blue    A
2    Red    D
3    Green    B
4    Orange    C

Then try the following query:

SELECT table_a.ID, color_validate.Code
FROM table_a, color_validate
WHERE (table_a.ID = color_validate.ID);

-Ken

<pre class="SCREEN">Hi,


You can use the construct <b>Case When</b> but You have to have
Your information structured (even if only in Your mind) 
in order to achive the results You want.

So, suposse You have for the color Blue the letter A,
for the color Red the letter D, for the color Green the 
letter B and finally for the color Orange the letter C.

For the following data:

create table dcosta.colors
(id     numeric(3),Color  varchar(12));

insert into dcosta.colors values(1, 'Blue');
insert into dcosta.colors values(2, 'Red');
insert into dcosta.colors values(3, 'Green');
insert into dcosta.colors values(4, 'Orange');

You can use the following instruction:

<b>
</b>SELECT ID, Color,       CASE WHEN color = 'Blue'   THEN 'A'           WHEN color = 'Red'    THEN 'D'           WHEN
color= 'Green'  THEN 'B'           WHEN color = 'Orange' THEN 'C'           ELSE 'other'      END
 
FROM dcosta.colors;</pre><br /> Obviously You can ommit the column Color from the select clause.<br /><br /><br /> Hope
Ihelped<br /> Dias Costa<br /><br /><br /><br /><br /> George Handin wrote: <blockquote
cite="mid44884752.7030303@dafunks.com"type="cite">Richard Broersma Jr wrote: <br /><blockquote type="cite"><blockquote
type="cite">ID   Color <br /> ---   ------- <br /> 1     Blue <br /> 2     Red <br /> 3     Green <br /> 4     Orange
<br/><br /> How would I rewrite the query to return results where the colors are replaced by letters to give the
followingresults? <br /><br /> ID    Color <br /> ---   ------- <br /> 1     A <br /> 2     D <br /> 3     B <br />
4    C <br /></blockquote><br /><br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html">http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html</a><br
/><br/> this is probably the easiest to implement but hard to mangage over time.  Another solution would <br /> be to
createcolor_code table that is referenced by your test table.  Then when you can create a <br /> query as: select a.ID,
b.codefrom test as a join color_code as b on a.color = b.color; <br /><br /> There are additional solutions to this
also.But these two are probably the easiest. <br /></blockquote><br /> Thanks! <br /><br />
---------------------------(endof broadcast)--------------------------- <br /> TIP 3: Have you checked our extensive
FAQ?<br /><br />               <a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a><br/></blockquote><br />