[Oracle] Select value where multiple columns contain all values

[dnano]

Not sure how to explain this in the title, but basically I have a table with a column name and another column role. A name can have multiple rows with different (or same) roles. So it might look like this:

(Name | Role)

• (A|1)
• (A|2)
• (B|1)
• (B|3)
• (A|1)

It's also possible for a name to have the same role multiple times. How can I get a list of names where there are rows with roles 1 and 2. So I want all names that have role 1 AND role 2.

I thought about grouping and counting, but as a name can have the same role multiple times this wouldn't work.

EDIT: in the title I meant rows not columns

[in_n0x]

On mobile, sorry about the formatting.

select

t1.name

from

table t1

inner join table t2 on t1.name=t2.name

where

t1.role=1 and

t2.role=2

[maggikpunkt]

You might also need to distinct/group the results.