oracle - 具有两列的 LISTAGG 函数

Question

我有一张这样的 table (报告)

--------------------------------------------------
|  user_id |  Department | Position  | Record_id |
--------------------------------------------------
|  1       |  Science    | Professor |  1001     |
|  1       |  Maths      |           |  1002     |
|  1       |  History    | Teacher   |  1003     |
|  2       |  Science    | Professor |  1004     |
|  2       |  Chemistry  | Assistant |  1005     |
--------------------------------------------------

我想得到以下结果

   ---------------------------------------------------------
   | user_id  |  Department+Position                       |
   ---------------------------------------------------------
   |  1       | Science,Professor;Maths, ; History,Teacher |
   |  2       | Science, Professor; Chemistry, Assistant   |
   ---------------------------------------------------------

这意味着我需要将空白空间保留为“”，如您在结果表中所见。
现在我知道如何使用 LISTAGG 函数，但仅限于一列。但是，我无法完全弄清楚如何同时处理两列。这是我的查询:

SELECT user_id, LISTAGG(department, ';') WITHIN GROUP (ORDER BY record_id)
FROM report

提前致谢 :-)

Answer 1

它只需要在聚合中明智地使用连接:

select user_id
     , listagg(department || ',' || coalesce(position, ' '), '; ')
        within group ( order by record_id )
  from report
 group by user_id

即聚合 department 的串联用逗号和 position并替换 position如果为 NULL，则带有空格。