Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a query which return the output like -- 5,4,6

Where 1 = apple, 2 = mango, 3 = banana, 4 = plum, 5 = cherry, 6 = kiwi etc.

I would like to update my output as cherry,plum,kiwi instead of 5,4,6 How can I achieve that in the same select statment.

I am okay to hard code the values.

Please confirm

May be I did explain clearly

Here is the sample

SELECT fruits FROM t_fruitid where id = 7

is returning me '5,6,4' as a single row

Now I want to update this single row output as 'cherry,plum,kiwi'

How do I do this

Thanku

share|improve this question

1 Answer 1

You should either query a reference table which has the integer values and the text strings in it (the preferred option) or use a case statement to do the conversion.

The preferred option

SELECT {YourExistingColumns}, NewTable.DescriptionColumn
FROM YourTable
JOIN NewTable ON YourTable.SomeIdColumn = NewTable.SomeIdColumn

Other option.

SELECT {YourExistingColumns}, CASE WHEN SomeColumn = 1 THEN 'apple' WHEN SomeColumn = 2 THEN 'mango' WHEN SomeColumn = 3 then 'banana' WHEN SomeColumn = 4 THEN 'plum' WHEN SomeColumn = 5 THEN 'cherry' WHEN SomeColumn = 6 THEN 'kiwi' END
FROM YourTable
share|improve this answer
    
Hi I updated by above description, please look at the below lines May be I did explain clearly Here is the sample SELECT fruits FROM t_fruitid where id = 7 is returning me '5,6,4' as a single row Now I want to update this single row output as 'cherry,plum,kiwi' How do I do this –  Tripz Jun 5 '12 at 20:41
    
SELECT REPLACE(fruits, 5, 'cherry') AS fruits_list FROM fruitid WHERE ID = 2 is doing for one value but I need to for all the values in the '5,6,4' trying for the approach to replace the ids with values in the output –  Tripz Jun 5 '12 at 20:50
    
I got it, I was able to achieve this using SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(fruits, 1, 'apple'), 2, 'mango'), 3, 'banana'), 4, 'plum'), 5, 'cherry'), 6, 'kiwi') Thanku –  Tripz Jun 5 '12 at 21:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.