I was browsing some of my older solutions, and I stumbled across this interesting piece.
The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'.
DECLARE @Roles TABLE (Person VARCHAR(5), [Role] VARCHAR(1))
SELECT 'Smith', 'O' UNION ALL
SELECT 'Smith', 'D' UNION ALL
SELECT 'Jones', 'O' UNION ALL
SELECT 'White', 'D' UNION ALL
SELECT 'Brown', 'X'
SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty
WHERE [Role] IN ('D', 'O')
GROUP BY Person