drop table #Sample

create table #Sample(Name Varchar(100),Role Varchar(10))

insert into #Sample values ('Vignesh' , 'Admin')

insert into #Sample values ('Vignesh' , 'User')

insert into #Sample values ('Bala' , 'Admin')

insert into #Sample values ('Bala' , 'User')

insert into #Sample values ('Suresh' , 'Admin')

insert into #Sample values ('Arun' , 'User')

insert into #Sample values ('Suresh' , 'Admin')

insert into #Sample values ('Arun' , 'User')

-- user has two or more rows in the table - may be same role

SELECT Name

FROM #SAMPLE

GROUP BY Name

HAVING COUNT(*) > 1;

-- user has two or more rows in the table - may be same role

WITH cteRoles

AS

(

SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,

Name,

Role

FROM#Sample

)

SELECTName

FROMcteRoles

WHERERowNr = 2

-- user has any two or more roles

SELECT s.Name

FROM #Sample s

WHERE EXISTS (

SELECT 1

FROM #Sample i

WHERE i.Name = s.Name

AND i.[Role] <> s.[Role]

)

-- user has two or more roles, including both Admin and User

SELECT Name

FROM (

SELECT Name, [Role]

FROM #Sample s

WHERE [Role] IN ('Admin','User')

GROUP BY Name, [Role]

) d

GROUP BY Name

HAVING COUNT(*) > 1

-- user has two or more roles, including both Admin and User

SELECT s.Name

FROM #Sample s

WHERE EXISTS (

SELECT 1

FROM #Sample i

WHERE i.Name = s.Name

AND i.[Role] = 'User'

)

AND s.[Role] = 'Admin'