Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle 11g Analytic Function - LISTAGG
-- Oracle 11g LISTAGG function enables listing of 
-- values by a delimitter such as , or ; etc. as shown
-- below (; used in the example below)

-- Base query (table)

SELECT * FROM
(SELECT  10 as bus_unit, 2000 sales from dual UNION ALL
 SELECT  10 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 1500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 1500 sales from dual UNION ALL
 SELECT  10 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  30 as bus_unit, 4500 sales from dual UNION ALL
 SELECT  30 as bus_unit, 3000 sales from dual UNION ALL
 SELECT  30 as bus_unit, 4000 sales from dual)
ORDER BY bus_unit;

-- Oracle 11g LISTAGG to create a ; separated sales list

SELECT
 qt_test.bus_unit,
 CAST(LISTAGG(qt_test.sales,'; ')
 WITHIN GROUP (ORDER BY qt_test.sales DESC) 
 AS VARCHAR2(30)) sales_values,
 SUM(qt_test.sales) sum_sales
FROM
(SELECT  10 as bus_unit, 2000 sales from dual UNION ALL
 SELECT  10 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 1500 sales from dual UNION ALL
 SELECT  20 as bus_unit, 1500 sales from dual UNION ALL
 SELECT  10 as bus_unit, 2500 sales from dual UNION ALL
 SELECT  30 as bus_unit, 4500 sales from dual UNION ALL
 SELECT  30 as bus_unit, 3000 sales from dual UNION ALL
 SELECT  30 as bus_unit, 4000 sales from dual
 ) qt_test
GROUP BY qt_test.bus_unit;

Oracle 11g LISTAGG output



Oracle 12c Release 2 (12.2) - LISTAGG

Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: Spetember 13, 2014

  39919