How to perform string aggregation/concatenation in Oracle?

Problem Statement:

You want to perform string concatenation as a comma delimited text in oracle.

Solution:

Oracle has few methods to perform string aggregation. The most common usuage you popularly find on internet is to convert multiple rows to a single row with a delimiter.

Starting Oracle version 11.2, a new built-in function is added called listagg which performs string aggregation. The listagg function uses two parameters the first is the string column or expression we want to aggregate and the second which is an optional parameter is the delimiter to put between the strings in the aggregated result. within group is mandatory and requires us to specify an order by clause that tells Oracle in which order the strings should be aggregated.

To demonstrate the usage of string aggregation we will use the students table to find out the resources under FI_ACCOUNT job role id.

Example

SELECT class_id,
       first_name || ' ' || last_name
FROM students
WHERE class_id = 'INS_STAFF' ;

When executed we have 5 resources under the job role. We will be use listagg function to convert these 5 reources from multiple rows to a single row with comma as tje delimiter.

INS_STAFF BROWN MICHAEL INS_STAFF JONES WILLIAM INS_STAFF MILLER DAVID INS_STAFF DAVIS RICHARD INS_STAFF GARCIA CHARLES

SQL: String aggregation

SELECT class_id,
       LISTAGG(first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name)
  FROM students
WHERE class_id = 'INS_STAFF'
GROUP BY class_id ;
INS_STAFF   BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID

We can further apply this on the entire table for all the Job Id.

Example

SELECT class_id,
       LISTAGG(first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name)
  FROM students
GROUP BY class_id ;

Output: Few rows from the SQL

CL_MATH ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON THOMAS CL_PHY RODRIGUEZ JOSEPH INS_CHAIRMAN SMITH JAMES INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID INS_VP JOHNSON JOHN, WILLIAMS ROBERT

The function listagg is easy to use and as a built-in highly performant. There are few drawbacks:

It cannot return a string larger than either 4,000 or 32,767 byte.
Prior to Oracle version 19c, it cannot do a distinct aggregation.
It does not exist in versions before 11.2.

One of the most common cases where the data won’t fit with in 4000 is that the data is not unique. If your database is version 19c or later, you can do distinct string aggregation, making the fewer occurrences possibly fit inside a varchar2.

SQL: String aggregation with Distinct Clause

SELECT class_id,
       LISTAGG( DISTINCT first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name)
  FROM students
GROUP BY class_id ;

Output

Data Preparation: Data used for the problem is shown below.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) 
    , email          VARCHAR2(40) 
    , phone_number   VARCHAR2(20)
    , join_date      DATE 
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4) 
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;

Kiran P

Updated on: 04-Dec-2020

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started