我有这样一张桌子(报告)
-------------------------------------------------- | 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
提前致谢 ：-)
在集合中只需要明智地使用连接：
select user_id,listagg(department || ',' || nvl(position,' '),'; ')
within group ( order by record_id )
from report
即通过逗号和位置聚合部门的连接,如果为空,则将其替换为空格.
