Seleccione una fila específica pero en función de otra función de columna

Probé y leí muchas publicaciones, pero aún no sé cómo manejar esta solicitud:

Tengo una tabla como la siguiente

+-------+---------------+------------+
| ID     |   Comp.    |    Role   |
+-------+---------------+------------+
| 1       |   abc         |     All     |
+-------+---------------+------------+
| 1       |   abc         |   Sales  |
+-------+---------------+------------+
| 2       |   def         |     All      |
+-------+---------------+------------+
| 3       |   zeh         |     All      |
+-------+---------------+------------+
| 3       |   zeh         |     TI      |
+-------+---------------+------------+

Me gustaría un resultado basado en roles, si una identificación específica tiene dos roles, uno es Todo y el otro es Ventas, entonces debo considerar Ventas fila

La salida debería ser algo como esto

+-------+---------------+------------+
| ID     |   Comp.    |    Role   |
+-------+---------------+------------+
| 1       |   abc         |   Sales  |
+-------+---------------+------------+
| 2       |    def        |       All    |
+-------+---------------+------------+
| 3       |   zeh         |     TI      |
+-------+---------------+------------+

Mostrar la mejor respuesta

Hmmm. . . Creo que esto hace lo que quieres:

select t.*
from t
where t.role = 'Sales'
union all
select t.*
from t
where t.role <> 'Sales' and
      not exists (select 1 from t t2 where t2.id = t.id);

Esto funcionará si solo tiene 2 valores para Rol ('Ventas' y 'Todos')

    create table #tmp(ID INT,Comp VARCHAR(10),Role VARCHAR(10))

    insert into #tmp
    SELECT 1,'abc','All'
    union ALL
    SELECT 1,'abc','Sales'
    union ALL
    SELECT 2,'def','All'


    select ID,Comp,MAX(Role) As Role from #tmp
    Group by Id,comp

    drop table #tmp

Según tengo entendido

SELECT ID,Comp,Role
FROM table1
WHERE ID IN(SELECT ID FROM table1 GROUP BY ID) AND Role != 'All'

UNION
SELECT ID,Comp,Role
FROM table1
WHERE ID IN(SELECT ID FROM table1 GROUP BY ID HAVING COUNT(ID)=1)

esto funciona bien, mira esta demostración

Si tiene otras funciones, debe agregarlas al CASE WHEN

SELECT
    ID
    , Comp
    , CASE WHEN role_id = 0 THEN 'All'
           WHEN role_id = 1 THEN 'Sales'
           WHEN role_id = 2 THEN 'TI' 
           ELSE NULL END AS Role
FROM
    (
    SELECT
        ID
        , Comp
        , MAX(CASE WHEN Role = 'All' THEN 0
                   WHEN Role = 'Sales' THEN 1
                   WHEN Role = 'TI' THEN 2 END 
                   ELSE -1 END) AS role_id
    FROM 
        t 
    GROUP BY
        ID
        , Comp
    )tmp