SQL: How to consolidate three records into one?

Question

I run a query to produce this.

username | userLastFirst    | email             | approver1         |department
MMickie  | Mouse, Mickie    | abc123@ships.com  | Doe, John         |HR
MMickie  | Mouse, Mickie    | abc123@ships.com  | Smith, Mike       |HR
MMickie  | Mouse, Mickie    | abc123@ships.com  | Turner, David     |HR

But how do I turn it into this:

username | userLastFirst    | email             | approver1    |approver2   |approver3    |department
MMickie  | Mouse, Mickie    | abc123@ships.com  | Doe, John    |Smith, Mike |Turner, David| HR

Instead of multiple rows of the same user with their approvers, I'm looking to consolidate it into one record with columns of their approvers.

(1) Tag your question with the database you are using. (2) What if there are more than three approvers? (3) Do you know the maximum number of approvers? — Gordon Linoff, Mar 04 '20 at 20:32

score 0 · Answer 1 · answered Mar 04 '20 at 20:38

A typical option is row_number and conditional aggregation:

select 
    username,
    userlastfirst,
    email,
    max(case when rn = 1 then approver end) approver1,
    max(case when rn = 2 then approver end) approver2,
    max(case when rn = 3 then approver end) approver3,
    department
from (
    select 
        t.*,
        row_number() over(partition by username order by approver) rn
    from mytable t
) t
group by 
    username,
    userfirstlast, 
    email,
    department

You can adjust the select clause with more max(case ... end) expressions to handle more than 3 supervisors per user and department. The key thing is that the query returns a fixed set of columns.

SQL: How to consolidate three records into one?

1 Answers1