How to suppress a field in select statement if it is equal to a second field in the same table 3

[rradelet]

If am trying to run a simple select query from one table:

SELECT FIELD1,FIELD2 FROM TABLEA

FIELD1 FIELD2
Blue Red
Green Green
Yellow Yellow
Orange Purple
Black Black

but if FIELD2 is equal to FIELD I want the result to look like:

FIELD1 FIELD2
Blue Red
Green
Yellow
Orange Purple
Black

 

[SkipVought]

Hi,
[pre]
SELECT
FIELD1
, case when field1=field2 then '' else field2 end
FROM TABLEA
[/pre]

 

[rradelet]

Thank you Skip.

I had been trying to use the case function, but I was little bit out on the exact syntax.

 

[gmmastros]

Skip's advice will work well. There is another method you can use that is similar.

The NullIf function accepts two parameters. If the parameters are the same value, NULL will be returned, otherwise the first parameter will be returned.

Ex:

Select NullIf('A', 'A')  -- This returns NULL
Select NullIf('X', 'Y')  -- This returns X

You could use it like this:

Select Field1,
       NullIf(Field2, Field1) As Field2
From   YourTableNameHere

This differs from Skip's advice in that his will return an empty string, while this will return NULL.

I only mention this because it's another way to accomplish something similar, and it always helps to know more.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

Thx George!