[SQL] Query to return modified results at runtime?

[George Handin]

I have a query:

SELECT * FROM testtable;

Where the results are:

IDColor
---   ---
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?


IDColor
---   ---
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

[SQL] Concat two fields into one at runtime

[George Handin]

Is there a way using built-in PostgreSQL functions to combine two data 
fields into a single field at runtime when querying data?


For example, the query now returns:

idfirstlast
---   ---  --
1 Goerge   Handin
2 Joe  Rachin

I'd like it to return:

idname
---   ---
1 George Handin
2 Joe Rachin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Query to return modified results at runtime?

[George Handin]

Richard Broersma Jr wrote:

IDColor
---   ---
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?


IDColor
---   ---
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!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq