Show All Users Who Have Multiple Managers

Inigo Montoya 586 Reputation points
2021-02-10T23:58:19.087+00:00

I have a database that holds user information. I need a way to find out what users have multiple managers. Below is a subset of data to illustrate the data structure.

Using MS SQL Server 2016 what would be a query to show me all userid and managerid where for each user the count of managerid > 1?

Declare @@Test Table
(
    userid varchar(100),
    mngrid varchar(100)
)


Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')

Select * FROM @@Test
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
{count} votes

2 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,561 Reputation points
    2021-02-11T02:07:20.733+00:00

    Hi @Inigo Montoya ,

    Please refer to:

    Declare @@Test Table  
    (  
        userid varchar(100),  
        mngrid varchar(100)  
    )  
    Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')  
      
    select *   
    from   
      (select *,count(mngrid) over(partition by userid) num   
      from @@Test)t  
    where num>1  
    

    Output:

    userid  mngrid  num  
    abc-413 619232 2  
    abc-413 39021 2  
    

    Or:

    Declare @@Test Table  
    (  
        userid varchar(100),  
        mngrid varchar(100)  
    )  
    Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')  
      
    ;with cte  
    as(select *,count(mngrid) over(partition by userid) num   
      from @@Test)  
    select * from cte  
    where num>1  
    

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Tom Phillips 17,686 Reputation points
    2021-02-15T17:53:04.957+00:00

    There are several ways to accomplish this query. But, I believe your teacher is looking for a HAVING clause:

    Select userid 
    FROM @@Test
    GROUP BY userid
    HAVING COUNT(DISTINCT mngrid) > 1
    
    0 comments No comments