Oracle 组合多行得到单个行

源表:

----------------------------------------
| Employee Name   | department | Emp Id |
----------------------------------------
| Sam             | Sales      | 101    |
----------------------------------------
| Sam             | Finance    | 101    |
----------------------------------------
| Dirk            | marketing  | 102    |
----------------------------------------
| Dirk            | Research   | 102    |
----------------------------------------

需要输出:

------------------------------------------------------
| Employee Name   | Emp Id | department1 | department2|
------------------------------------------------------
| Sam             | 101    | Sales       | Finance    |
------------------------------------------------------
| Dirk            | 102    | marketing   | Research   |
------------------------------------------------------

您能否提供帮助，我应该使用哪些功能或查询来获取上述输出？

听起来你想转移。

如果要为员工拥有的N个部门中的每个部门提供一列，则:

-在子查询中为每个部门/员工分配行号，从每个员工的一个开始
-为pivot子句中的每个行号取最大部门名称

这看起来像:

with rws as (
  select t.*,
         row_number() over (
           partition by emp_id
           order by department
         ) rn
  from   ... t
)
  select * from rws
  pivot (
    max ( department ) dept 
    for rn in ( 1, 2 )
  )

有关如何使用pivot的完整详细信息，请参见https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot