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 :-)
Usage of the LISTAGG Function There are a few ways you can use this function. If you use it without any grouping, LISTAGG operates on all rows and returns a single row. If you use it with grouping, LISTAGG operates on and returns a row for each group defined by the GROUP BY clause.
The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause. As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
The Oracle LISTAGG() function is an aggregation function that transforms data from multiple rows into a single list of values separated by a specified delimiter.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With