Posted by alberto.estrada1 on 02/03/07 08:10

I have a unique situation. My data looks something like:

Test 1
Test 2
Test 3
Test 2
Test 1
Test 300
Test 200
Test 1
Test 300
Test 200

I want to display all of the above like:

Test 1
Test 2
Test 3
Test 2
Metals
Test 1
Test 1

Notice that I have everything displayed except for 'Test 200' and
'Test 300'. This have been replaced with the word 'Metals' and I'm
only displaying it one time, no matter how often Test 200 shows up, or
Test 300.

My code looks like the following. It works good, except if I have to
add 'Test 400' etc...I would have to hard code them and it will build
up real quick. I looked at GROUPs to see if that would help, but I
don't think it would because it has to be displayed with a different
name such as 'Metals' for all the tests...Test 100, 200, etc... Is
there a better way without having to add each number in it? I think
the best way is to use a LIKE statment where the WHEN is being used
but I keep getting errors if I use the LIKE sytnax where the WHEN is
being used.

SELECT sample
FROM mysamples
WHERE (sample <> 'TEST 200') AND (sample <> 'TEST 300')

UNION ALL

SELECT DISTINCT
SAMPLE = CASE Sample WHEN 'test 200' THEN 'Metals' WHEN 'Test 300'
THEN 'Metals'
END
FROM MYSAMPLES
WHERE (sample = 'TEST 200') OR
(sample = 'TEST 300')



Any help is appreciated...

• Next in forum: Re: I don't understand this "server timeout"
• Prev in forum: Re: I don't understand this "server timeout"
• Thread view: Using LIKE in a CASE statement

[Reply to this message]