How to display multiple column values in a Row

What is string Aggregation?

String aggregation is simply the grouping and concatenation of multiple rows of data into a single row per group. 

The LISTAGG function has the following syntax structure:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

• the column or expression to be aggregated;
• the WITHIN GROUP keywords;
• the ORDER BY clause within the grouping.

We will now see some examples of the function below.

select

   deptno,

   listagg (ename, ',') 

WITHIN GROUP 

(ORDER BY ename) empnames

FROM 

   emp

GROUP BY 

   deptno

Output:

 DEPTNO    EMPNAMES                                      
---------- --------------------------------------------------
        10 CLARK,KING,MILLER                          
        20 ADAMS,FORD,JONES,SCOTT,SMITH         
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 

Share on Google Plus

About Mindsforest