SQL:如何将三条记录合并为一条

Question

我运行一个查询来生成这个。

username | userLastFirst    | email             | approver1         |department
MMickie  | Mouse, Mickie    | [e-mail]  | Doe, John         |HR
MMickie  | Mouse, Mickie    | [e-mail]  | Smith, Mike       |HR
MMickie  | Mouse, Mickie    | [e-mail]  | Turner, David     |HR

但是我该如何把它变成这样：

username | userLastFirst    | email             | approver1    |approver2   |approver3    |department
MMickie  | Mouse, Mickie    | [e-mail]  | Doe, John    |Smith, Mike |Turner, David| HR

我希望将同一用户的多行及其审批人合并为一个记录，其中包含审批人的列。

Answer 1

一个典型的选项是row_number和条件聚合：

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 

您可以使用更多的max(case ... end)表达式来调整select子句，以便每个用户和部门处理3个以上的主管。关键是查询返回一组固定的列。