Aggregating data from a number of rows into a single rows & Converting rows into columns in Oracle

For Aggregating data from a number of rows into a single row we have various functions in oracle some of them are listagg function, xmlagg and wm_concat function

Consider the following example

Dept No	Emp Name
10	John
11	Neo
12	Anderson
12	Trinity
11	Morpheous
12	Smith

Now we want the data in the following way aggregated based on Dept No

Dept No	Emp Name
10	John
11	Morpheous,Neo
12	Anderson,Smith,Trinity

We can achieve this by using the LISTAGG function in the following way

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno;

Using XMLAGG function

SELECT deptno,

 RTRIM ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()') , ',' ) AS EMPNAME

FROM   emp

GROUP BY deptno;

Using WM_CONCAT

SELECT deptno, wm_concat(ename) AS employees

FROM   emp  GROUP BY deptno;

Above three functions yield the same result.

For converting rows into columns we have Pivot function in oracle

Syntax is as follows

SELECT * FROM

(

  SELECT column1, column2

  FROM tables

  WHERE conditions

)

PIVOT

(

  aggregate_function(column2)

  FOR column2

  IN ( expr1, expr2, ... expr_n) | subquery

)

ORDER BY expression [ASC/DESC];

Consider the following data for example

order_id	customer_id	product_id
50001	SMITH	10
50002	SMITH	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	SMITH	20
50008	SMITH	10
50009	SMITH	20

The expected output is

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3

The query will be as follows

SELECT * FROM

(

  SELECT customer_ref, product_id

  FROM orders

)

PIVOT

(

  COUNT(product_id)

  FOR product_id IN (10, 20, 30)

)

ORDER BY customer_ref:

Hope this article helps you in understanding the above functions