String Aggregation Techniques

==============================================

STEP1:--CREATE TABLE EMP (DEPTNO NUMBER, ENAME VARCHAR2(20));

STEP2:-- SELECT * FROM EMP;

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


STEP3: NO WE WRITE A QUERY FOR BELLOW FORMAT OUTPUT.


Desired Output:

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

STEP4: QUERY

FOR 11G

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


FOR 10G

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

Age in Years and months and days:

===================================

select
trunc
( months_between( to_date('14-05-2014','DD-MM-YYYY'), to_date('24-10-1986','DD-MM-YYYY') ) /12 ) Years,
mod
( trunc( months_between( to_date('14-05-2014','DD-MM-YYYY'), to_date('24-10-1986','DD-MM-YYYY') ) ), 12 ) months,
to_date(
'14-05-2014','DD-MM-YYYY') -
add_months(to_date(
'24-10-1986','DD-MM-YYYY'),trunc( months_between( to_date('14-05-2014','DD-MM-YYYY'),
to_date(
'24-10-1986','DD-MM-YYYY') ) )) days
from
dual ;