Write View将包含两列的表转换为包含多列

Question

如何为表1编写SQL查询以显示VIEW并获得类似第二表的输出?

TABLE 1
+-------------+---------------+
|EMPLOYER ID  | EMPLOYEE NAME |
+-------------+---------------+
|1            |JOHN           |
+-------------+---------------+
|1            |MICHAEL        |
+-------------+---------------+
|1            |FRANK          |
+-------------+---------------+
|2            |JOHN           |
+-------------+---------------+
|2            |JACK           |
+-------------+---------------+

我想写一个视图来显示这样一个表吗?

+-------------+----------------+----------------+---------------+
|EMPLOYER ID  | EMPLOYEE NAME 1| EMPLOYEE NAME 2|EMPLOYEE NAME 3|
+-------------+----------------+----------------+---------------+
|1            | JOHN           | MICHAEL        | FRANK         |
+-------------+----------------+----------------+---------------+
|2            | JOHN           | JACK           | NULL          |
+-------------+----------------+----------------+---------------+

Answer 1

您可以使用条件聚合:

select employer_id,
max(case when seqnum = 1 then employee_name end) as employee_name_1,
max(case when seqnum = 2 then employee_name end) as employee_name_2,
max(case when seqnum = 3 then employee_name end) as employee_name_3
from (select t1.*,
row_number() over (partition by employer_id order by employee_name) as seqnum
from table1 t1
) t1
group by employer_id;

这适用于三个名称(显然可以扩展到更多)。但是，如果您不知道名称的数量，那么将它们组合成一个字符串可能会更方便:

select employer_id,
group_concat(employee_name) as employee_names
from table1
group by employer_id;