LISTAGG function with two columns
oracle, oracle11g, plsql, plsqldeveloper
Solution
It just requires judicious use of concatenation within the aggregation:
select user_id
, listagg(department || ',' || coalesce(position, ' '), '; ')
within group ( order by record_id )
from report
group by user_id
i.e. aggregate the concatentation of `department` with a comma and `position` and replace `position` with a space if it is NULL.
Problem
I have one table like this (report) ``` -------------------------------------------------- | 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 | -------------------------------------------------- ``` I'd like to have the following result ``` --------------------------------------------------------- | user_id | Department+Position | --------------------------------------------------------- | 1 | Science,Professor;Maths, ; History,Teacher | | 2 | Science, Professor; Chemistry, Assistant | --------------------------------------------------------- ``` That means I need to preserve the empty space as ' ' as you can see in the result table. Now I know how to use LISTAGG function but only for one column. However, I can't exactly figure out how can I do for two columns at the sametime. Here is my query: ``` SELECT user_id, LISTAGG(department, ';') WITHIN GROUP (ORDER BY record_id) FROM report ``` Thanks in advance :-)