Collapsing a column to yes/no on query I have a table with two columns name and value.

There are many rows with the same name, but with different values.

I want to create a function that returns a table with 2 columns.  The first column is the name and the second is a character, either yes or no, which refers to whether a name has a specific value or not.

If a name  has the specific value at least at one of its values to return yes and if it does not have the specific value to return no.



With the answers so far, I return the same name many times with the corresponding values like

('BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'YES','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO') for example.

I want to return a table like that: 

('BOB'-'YES','JAME'-'NO') 

because bob has a specific value that I want at least one time at his values and JAME has not the specific value in a row.
Here are three simple methods to do it,


Returning bool: Just use a simple CASE statement.

SELECT name, CASE WHEN value = 'foo' THEN true ELSE false END
FROM table;

Returning bool compact: You can actually make it more terse, so long as value is NOT NULL. That CASE statement is essentially equal to

SELECT name, value = 'foo'
FROM table;


If value is ever null though you'll get null rather than false.
Returning text: If you insist on yes and no instead (I wouldn't do that), you can use strings instead of the special-made boolean type (there is no more compact syntax to write this).

SELECT name, CASE WHEN value = 'foo' THEN 'yes' ELSE 'no' END
FROM table;