You are here: Using LIKE in a CASE statement « MsSQL Server « IT news, forums, messages
Using LIKE in a CASE statement

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация