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

(alberto.estrada1@gmail.com) writes:
> 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.

Without knowing the exact rules, it's difficult to give a whole-
covernig answer. But the condition

sample LIKE 'TEST [0-9][0-9]{0-9]'

could be used to handle all with Test plus a three-digit number.

SELECT sample
FROM mysamples
WHERE sample NOT LIKE 'TEST [0-9][0-9]{0-9]'
UNION ALL
SELECT DISTINCT 'Metals'
FROM MYSAMPLES
WHERE sammple LIKE LIKE 'TEST [0-9][0-9]{0-9]'

But I would suspect that your real business problem have a different
solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> I have a unique situation. <<

Probably not!! Trust me!

>> My data looks something like: <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

On Feb 3, 2:19 pm, "--CELKO--" <jcelko...@earthlink.net> wrote:
> >> I have a unique situation. <<
>
> Probably not!! Trust me!
>
> >> My data looks something like: <<
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.

Thanks Erland, your solution did it. I don't understand how the
second part of the syntax: SELECT DISTINCT 'Metals'
FROM MYSAMPLES

shows up as 'METALS'. I thought i would have had to select an AS
somewhere...nonetheless it worked!!

(alberto.estrada1@gmail.com) writes:
> Thanks Erland, your solution did it. I don't understand how the
> second part of the syntax: SELECT DISTINCT 'Metals'
> FROM MYSAMPLES
>
> shows up as 'METALS'. I thought i would have had to select an AS
> somewhere...nonetheless it worked!!

Not sure what you mean. If it says METALS in all uppercase in the output,
something very strange is going on.

If you mean the column name, in a SELECT UNION statement the column
names are derived from the first SELECT, so there is no need to
provide column names for the subsequent SELECT:s.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx