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))

INSERT      @Roles
SELECT      'Smith', 'O' UNION ALL
SELECT      'Smith', 'D' UNION ALL
SELECT      'Jones', 'O' UNION ALL
SELECT      'White', 'D' UNION ALL
SELECT      'Brown', 'X'

SELECT      Person,
            SUBSTRING('ODB', SUM(CHARINDEX(Role, 'OD')), 1) AS Duty
FROM        @Roles
WHERE       [Role] IN ('D', 'O')
GROUP BY    Person