Finding data from same table

My table is :

loginid	name	role
12	abc	analyst
12	abc	clerk
142	xyz	clerk
142	xyz	manager
1	yash	manager
1	yash	ceo
2	aarav	president
2	aarav	ceo

I want to display each login id once and if it has two or more than two roles it is expected to show this result :

loginid	name	role	role
12	abc	clerk	analyst
142	xyz	manager	clerk
1	yash	ceo	manager
2	aarav	ceo	president

but then I was able to write following query :

select a.loginid,a.name,a.role,b.role 
from tb1 a,tb1 b 
where a.loginid=b.loginid and a.role<>b.role;

what can I do to resolve the issue? as the result i am getting is :

loginid	name	role	role
12	abc	clerk	analyst
12	abc	analyst	clerk
142	xyz	manager	clerk
142	xyz	clerk	manager
1	yash	ceo	manager
1	yash	manager	ceo
2	aarav	ceo	president
2	aarav	president	ceo

Answer

I would suggest that you aggregate the roles into a single column:

select loginid, name, group_concat(role order by role) as roles
from t
group by loginid, name;

If you want exactly two columns for the roles (as in the question you asked), you can also use min() and max():

select loginid, name, min(role), nullif(max(role), min(role))
from t
group by loginid, name;