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

[Dias Costa]

Hi, You can use the construct Case When 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: 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; Obviously You can ommit the column Color from the select clause. Hope I helped Dias Costa George Handin wrote: 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! ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?               http://www.postgresql.org/docs/faq