Retrieving rows that have different values for a column in unnormalized table

Given the following fruit table, I wanted to write sql query that returns Fruits that have rating of both A and B. In the example, the answer should give Orange and Lemon since they have both A & B ratings. Apple & Pear, though they have A and B rating, they should not be included in the result as they don’t contain both. One way to go about is to use stored procedure, but I wanted to write sql statement that is extensible to any number of ratings (not only A & B).

Fruit     Rating
Orange      A
Orange      B
Orange      C
Lemon       A
Lemon       B
Pear        B
Pear        C
Pear        C
Apple       A
Apple       C

Expected result:

Fruit    Rating
Orange      A
Orange      B
Lemon       A
Lemon       B

Answer

This works in Oracle. It requires support for DISTINCT in window functions.

with data (Fruit,Rating) as (
  select 'Orange', 'A' from dual union all
  select 'Orange', 'B' from dual union all
  select 'Orange', 'C' from dual union all
  select 'Lemon', 'A' from dual union all
  select 'Lemon', 'B' from dual union all
  select 'Pear', 'B' from dual union all
  select 'Pear', 'C' from dual union all
  select 'Pear', 'C' from dual union all
  select 'Apple', 'A' from dual union all
  select 'Apple', 'C' from dual
)
select fruit, rating
from (
  select fruit, rating, cnt, max(cnt) over() max_cnt
  from (
    select fruit, rating,
      count(distinct rating) over(partition by fruit) cnt
    from data where rating in ('A','B')
  )
)
where cnt = max_cnt;