Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Tag Archives: Analytic Function

LISTAGG and PIVOT functions in Oracle 11g R2

Analytic functions are first introduced in Oracle 8i. They have got few functions in new release. The first function which I will discuss here will be LISTAGG. It creates a delimited list within a group. Some times you need to toil hard for this. Most of the times, grouping by and converting columns to rows.

With Oracle 11g R2, all you need is the following.

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from hr.employees
  6    group by deptno
  7    order by deptno
  8   /

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

You can decide what to append with. The syntax is LISTAGG(column, character). Read more of this post

Width Bucket – Analytic Function

WIDTH_BUCKET function is used to assign bucket value for a list of values based on maximum and minimum number. This is one of the new analytic function introduced in Oracle 10g. Consider the following table.

StudentId Mark
1 56
2 43
3 76
4 89
5 90
6 91
7 49
8 63
9 83
10 74
Read more of this post