listagg in Oracle 11g

Oracle 11gR2 listagg built-in function allows for many table columns to be displayed within a single row, a non-first-normal form display

Syntax :

     listagg(column_value,'delimiter') WITHIN GROUP(ORDER BY column_name)


--Example 1:

CREATE TABLE test_ins(empid NUMBER,product VARCHAR2(30));

BEGIN
   INSERT INTO test_ins VALUES( 101,'pendrive');
   INSERT INTO test_ins VALUES( 102,'toy');
   INSERT INTO test_ins VALUES( 101,'ipod');
   INSERT INTO test_ins VALUES( 102,'hat');
   INSERT INTO test_ins VALUES( 103,'cpu');
   INSERT INTO test_ins VALUES( 104,'pen');
   INSERT INTO test_ins VALUES( 104,'car');
   INSERT INTO test_ins VALUES( 104,'mat');
   INSERT INTO test_ins VALUES( 105,'tv');
   INSERT INTO test_ins VALUES( 106,'laptop');
   commit;
END;

SELECT * FROM test_ins;

    SELECT empid "Employee",
           listagg(product,
               ',') within
    GROUP (ORDER BY empid) "Products"
     FROM test_ins
 GROUP BY empid;

-->-- Result set --<--

 

DROP TABLE test_ins;

-->-- Example 2

select department_id "Department id", listagg(first_name,', ') WITHIN GROUP(ORDER BY employee_id) "Employees"
FROM employees
WHERE department_id IN (10,20,30,60)
GROUP BY department_id;

-->-- Result set --<--