LISTAGG Analytic Functions in Oracle SQL
LISTAGG Function
LISTAGG functions is used to concatenate the column data into one line by placing any separator in between data.
Examples of LISTAGG function:
Concatenate the column data in one row without separator
set wrap on
col emp_list for a60
SELECT LISTAGG(last_name) "EMP_LIST" FROM employees
EMP_LIST ------------------------------------------------------------ AbelAndeAtkinsonAustinBaerBaidaBandaBatesBellBernsteinBissot BloomBullCabrioCambraultCambraultChenChungColmenaresDaviesDe HaanDellingerDillyDoranErnstErrazurizEverettFavietFayFeeney FleaurFoxFrippGatesGeeGeoniGietzGrantGrantGreenbergGreeneHal lHartsteinHigginsHimuroHunoldHuttonJohnsonJonesKauflingKhooK ingKingKochharKumarLadwigLandryLeeLivingstonLorentzMallinMar kleMarlowMarvinsMatosMavrisMcCainMcEwenMikkilineniMourgosNay erOConnellOlsenOlsonOzerPartnersPataballaPatelPerkinsPhiltan
Concatenate with comma separator
set wrap on
col EMP_LIST for a60
SELECT LISTAGG(last_name, '; ') "EMP_LIST" FROM employees
EMP_LIST ------------------------------------------------------------ Abel; Ande; Atkinson; Austin; Baer; Baida; Banda; Bates; Bel l; Bernstein; Bissot; Bloom; Bull; Cabrio; Cambrault; Cambra ult; Chen; Chung; Colmenares; Davies; De Haan; Dellinger; Di lly; Doran; Ernst; Errazuriz; Everett; Faviet; Fay; Feeney; Fleaur; Fox; Fripp; Gates; Gee; Geoni; Gietz; Grant; Grant; Greenberg; Greene; Hall; Hartstein; Higgins; Himuro; Hunold; Hutton; Johnson; Jones; Kaufling; Khoo; King; King; Kochhar;
Concatenate by using group by clause and producing a comma-separated list of employees for each department
set wrap on
col EMP_LIST for a60
SELECT department_id , LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY last_name) "EMP_LIST" FROM employees
group by department_id order by 1;
DEPARTMENT_ID EMP_LIST
------------- ------------------------------------------------------------
10 Whalen
20 Fay; Hartstein
30 Baida; Colmenares; Himuro; Khoo; Raphaely; Tobias
40 Mavris
50 Atkinson; Bell; Bissot; Bull; Cabrio; Chung; Davies; Delling
er; Dilly; Everett; Feeney; Fleaur; Fripp; Gates; Gee; Geoni
; Grant; Jones; Kaufling; Ladwig; Landry; Mallin; Markle; Ma
rlow; Matos; McCain; Mikkilineni; Mourgos; Nayer; OConnell;
Olson; Patel; Perkins; Philtanker; Rajs; Rogers; Sarchand; S
eo; Stiles; Sullivan; Taylor; Vargas; Vollman; Walsh; Weiss
Handle error ORA-01489: result of string concatenation is too long
set wrap on
col EMP_LIST for a60
SELECT department_id , LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY last_name) "EMP_LIST" FROM employees CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
where department_id = 50
group by department_id order by 1;
ERROR at line 2:
ORA-01489: result of string concatenation is too long
Solution:
Use the ON OVERFLOW TRUNCATE like LISTAGG(col_name, ‘,’ ON OVERFLOW TRUNCATE) within the LISTAGG to handle the ORA-01489 error.
Note: Two other way to use:
LISTAGG(ename, ‘,’ ON OVERFLOW TRUNCATE ‘~~~’)
Output : it will give output of query with ‘~~~’ instead ‘…’ .
LISTAGG(ename, ‘,’ ON OVERFLOW TRUNCATE ‘…’ WITHOUT COUNT)
output : it will not give output with count at last in bracket.
set wrap on
col EMP_LIST for a60
SELECT department_id , LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE)
WITHIN GROUP (ORDER BY last_name) "EMP_LIST" FROM employees CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
where department_id = 50
group by department_id order by 1;
DEPARTMENT_ID EMP_LIST
------------- ------------------------------------------------------------
50 Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
..........................................................
...........................................................
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; Atkinson; Atkinson; Atkinson; Atkinson; Atkinson;
Atkinson; ...(44603)
