-- 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;
17653