SQL: SQL Exclusive Or

Question

I have a table in the form:

username | role
---------+------
name1    | internal
name1    | admin
name2    | internal
name3    | admin
name4    | external

I need to find all users who have the role of "internal" or "admin", but do not have both (essentially XOR). How to do it in SQL?

A query that results in a form similar to the one below would be ideal:

username | internal | admin
---------+----------+-------
name2    | 1        | 0
name3    | 0        | 1

If this helps, I use the Oracle database

Answer 1

I would approach this with conditional aggregation:

select username,
       max(case when role = 'internal' then 1 else 0 end) as internal,
       max(case when role = 'admin' then 1 else 0 end) as admin
from t
where role in ('internal', 'admin')
group by username
having count(*) = 1;

If name / role pairs can be duplicated, use having count(distinct role) = 1.

Answer 2

try it

SELECT UserName, 
CASE WHEN Role = 'internal' Then 1 Else 0 END internal,
CASE WHEN Role = 'admin' Then 1 Else 0 END admin
FROM (
SELECT A.*, COUNT(DISTINCT Role) OVER (PARTITION BY UserName) CNT
FROM Users A
) WHERE CNT = 1

SQL FIDDLE