LISTAGG Analytic Functions in Oracle SQL

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.