Collapsing a column to yes/no on query

Question

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.

Answer 1

Here are three simple methods to do it,

1. Returning bool: Just use a simple CASE statement.

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

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

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

Answer 2

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.

You left room for interpretation, but this seems to be what you are after:

SELECT name, bool_or(value = 'specific_value') AS has_specific_value
FROM   tbl
GROUP  BY name;

The manual:

bool_or(expression) ... true if at least one input value is true, otherwise false.

You get one row per distinct name and a flag indicating whether the name has the given value in at least one of its rows.

You might want to add count(*) to also get the number of rows per name.
count(*) FILTER (WHERE value = 'specific_value') to get the count of matching rows would only make sense where there can be multiple matches, i.e. no UNIQUE constraint on (name, value) or similar - "but with different values" seems to indicate as much.
And maybe append ORDER BY ...