Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LISTAGG function with two columns

Tags:

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 :-)

like image 217
Jaanna Avatar asked Dec 14 '12 10:12

Jaanna


People also ask

Can I use Listagg without GROUP BY?

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.

Is Listagg an aggregate function?

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.

What does Listagg do in Oracle?

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.


1 Answers

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.

like image 157
Ben Avatar answered Oct 15 '22 07:10

Ben