From time to time I’m asked to aggregate strings from multiple records into a single column using SQL. Here’s an example, showing a comma separated list of ordered employee names per department based on the famous EMP and DEPT tables.

DEPTNO

DNAME

ENAME_LIST

10

ACCOUNTING

CLARK, KING, MILLER

20

RESEARCH

ADAMS, FORD, JONES, SCOTT, SMITH

30

SALES

ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

40

OPERATIONS

Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you may use LISTAGG go for it, but if you have to work with an older version of the Oracle Database Server you might be interested in some other options which I discuss per Oracle Database version.

Oracle7

More than twenty years ago PL/SQL was introduced as part of the Oracle Database Server version 7.0 allowing to write functions to be used in SQL statements. Back than something like the following was necessary to build comma separated values:

1) Oracle7 UDF Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATEORREPLACEFUNCTIONdeptno_to_ename_list(in_deptnoINVARCHAR2)

RETURNVARCHAR2IS

CURSORl_curIS

SELECTename

FROMemp

WHEREdeptno=in_deptno

ORDERBYename;

l_retVARCHAR2(2000);

BEGIN

FORl_recINl_cur

LOOP

IFl_cur%ROWCOUNT>1THEN

l_ret:=l_ret||', ';

END IF;

l_ret:=l_ret||l_rec.ename;

END LOOP;

RETURNl_ret;

END;

/

SELECTdeptno,dname,deptno_to_ename_list(deptno)ASename_list

FROMdept

ORDERBYdeptno;

Oracle8

Version 8.0 came with the object option allowing to solve the problem in more generic ways.

2) Oracle8 Collection Type Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

CREATEORREPLACETYPEstring_tabtypeISTABLEOFVARCHAR2(2000);

CREATEORREPLACEFUNCTIONcollection_to_comma_list(

in_stringsINstring_tabtype

)RETURNVARCHAR2IS

l_retVARCHAR2(2000);

BEGIN

IFin_strings.COUNT>0THEN

FORiIN1..in_strings.COUNT

LOOP

IFi>1THEN

l_ret:=l_ret||', ';

END IF;

l_ret:=l_ret||in_strings(i);

END LOOP;

END IF;

RETURNl_ret;

END;

/

SELECTd.deptno,

d.dname,

collection_to_comma_list(

CAST(

MULTISET(

SELECTename

FROMempe

WHEREe.deptno=d.deptno

ORDERBYename

)ASstring_tabtype

)

)ASename_list

FROMdeptd

ORDERBYd.deptno;

Another option was to use a REF CURSOR instead of a collection type. The PL/SQL part was executable in Oracle7 too, but the CURSOR expression was not available back than. BTW: SYS_REFCURSOR was introduced in 9.0, so this specific PL/SQL type is really necessary with version 8.0.

3) Oracle8 RefCursor Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

CREATE ORREPLACE PACKAGEmytypes_pkg IS

TYPE refcursor_type ISREF CURSOR;

ENDmytypes_pkg;

/

CREATE ORREPLACE FUNCTIONcursor_to_comma_list(

in_refcursor INmytypes_pkg.refcursor_type

)RETURNVARCHAR2 IS

l_string VARCHAR2(2000);

l_ret VARCHAR2(2000);

BEGIN

LOOP

FETCH in_refcursor INTO l_string;

EXIT WHEN in_refcursor%NOTFOUND;

IFin_refcursor%ROWCOUNT>1THEN

l_ret:=l_ret||', ';

ENDIF;

l_ret:=l_ret||l_string;

ENDLOOP;

CLOSE in_refcursor;

RETURNl_ret;

END;

/

SELECTd.deptno,

d.dname,

cursor_to_comma_list(

CURSOR(

SELECT ename

FROM empe

WHEREe.deptno=d.deptno

ORDER BY ename

)

)ASename_list

FROM deptd

ORDER BYd.deptno;

Oracle9i Release 1

Version 9.0 came with basic XML support which allowed to aggregate strings without the need of a helper function.

4) Oracle9iR1 SYS_XMLAGG/String Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

SELECTd.deptno,

d.dname,

RTRIM(

SYS_XMLAGG(

SYS_XMLGEN(

e.ename||', '

)

).EXTRACT(

'/ROWSET/ROW/text()'

).getStringVal(),

', '

)ASename_list

FROMdeptd

LEFT JOINempeONe.deptno=d.deptno

GROUP BYd.deptno,d.dname

ORDERBYd.deptno,d.dname;

In this solution the sort order of the aggregated strings is not definable. Version 9i introduced also user-defined aggregate functions. To use them you need to implement the ODCIAggregate interface which allows you also to sort the result.

5) Oracle9iR1 Aggregate Function Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

CREATEORREPLACETYPEstring_tabtypeASTABLEOFVARCHAR2(2000);

CREATEORREPLACETYPEmylistagg_typeASOBJECT(

stringsstring_tabtype,

STATICFUNCTIONODCIAggregateInitialize(

sctxINOUTmylistagg_type

)RETURNNUMBER,

MEMBERFUNCTIONODCIAggregateIterate(

SELFINOUTmylistagg_type,

valueINVARCHAR2

)RETURNNUMBER,

MEMBERFUNCTIONODCIAggregateTerminate(

SELFINmylistagg_type,

returnValueOUTVARCHAR2,

flagsINNUMBER

)RETURNNUMBER,

MEMBERFUNCTIONODCIAggregateMerge(

SELFINOUTmylistagg_type,

ctx2INmylistagg_type)

RETURNNUMBER

);

CREATEORREPLACETYPEBODYmylistagg_typeIS

STATICFUNCTIONODCIAggregateInitialize(

sctxINOUTmylistagg_type

)RETURNNUMBERIS

BEGIN

sctx:=mylistagg_type(string_tabtype());

RETURNODCIConst.Success;

END;

MEMBERFUNCTIONODCIAggregateIterate(

SELFINOUTmylistagg_type,

valueINVARCHAR2

)RETURNNUMBERIS

BEGIN

SELF.strings.EXTEND;

SELF.strings(SELF.strings.COUNT):=VALUE;

RETURNODCIConst.Success;

ENDODCIAggregateIterate;

MEMBERFUNCTIONODCIAggregateTerminate(

SELFINmylistagg_type,

returnValueOUTVARCHAR2,

flagsINNUMBER

)RETURNNUMBERIS

l_sorted_stringsstring_tabtype;

l_return_valueVARCHAR2(2000);

BEGIN

SELECTCOLUMN_VALUE

BULKCOLLECTINTOl_sorted_strings

FROMTABLE(strings)

ORDERBYCOLUMN_VALUE;

FORiIN1..l_sorted_strings.COUNT

LOOP

IFi>1THEN

l_return_value:=l_return_value||', ';

END IF;

l_return_value:=l_return_value||l_sorted_strings(i);

END LOOP;

returnValue:=l_return_value;

RETURNODCIConst.Success;

ENDODCIAggregateTerminate;

MEMBERFUNCTIONODCIAggregateMerge(

SELFINOUTmylistagg_type,

ctx2INmylistagg_type

)RETURNNUMBERIS

BEGIN

FORiIN1..ctx2.strings.COUNT

LOOP

SELF.strings.EXTEND;

SELF.strings(SELF.strings.COUNT):=ctx2.strings(i);

END LOOP;

RETURNODCIConst.Success;

ENDODCIAggregateMerge;

END;

/

CREATEORREPLACEFUNCTIONmylistagg(

in_stringINVARCHAR2

)RETURNVARCHAR2PARALLEL_ENABLEAGGREGATEUSINGmylistagg_type;

/

SELECTd.deptno,d.dname,mylistagg(e.ename)ASename_list

FROMdeptd

LEFT JOINempeONe.deptno=d.deptno

GROUP BYd.deptno,d.dname

ORDERBYd.deptno,d.dname;

Oracle9i Release 2

Version 9i Release 2 came with SQL/XML support and the function XMLAGG which replaces SYS_XMLAGG and allows to sort the elements to be aggregated (see line 7).

6) Oracle9iR2 XMLAGG/String Style

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECTd.deptno,

d.dname,

RTRIM(

XMLAGG(

XMLELEMENT(

"e",e.ename||', '

)ORDERBYe.ename

).EXTRACT(

'/e/text()'

).getStringVal(),', '

)ASename_list

FROMdeptd

LEFT JOINempeONe.deptno=d.deptno

GROUP BYd.deptno,d.dname

ORDERBYd.deptno,d.dname;

One may argue that using RTRIM to get rid of the last comma is not the way to interact with XML, especially since SQL/XML supports XSLT. But probably no one can deny that writing the appropriate stylesheet is a bit more complex and time-consuming. Nonetheless, here’s a XSLT example:

Oracle Database 11g Release 2

As mentioned at the beginning, in version 11g Release 2 Oracle finally introduced the aggregate function LISTAGG to conveniently aggregate strings.

8) Oracle 11gR2 ListAgg Style

1

2

3

4

5

6

7

8

9

10

11

SELECTd.deptno,

d.dname,

LISTAGG(

e.ename,', '

)WITHINGROUP(

ORDERBYe.ename

)ASename_list

FROMdeptd

LEFT JOINempeONe.deptno=d.deptno

GROUP BYd.deptno,d.dname

ORDERBYd.deptno,d.dname;

Performance Comparison

To compare the runtime performance of the different solution approaches I created 1 million rows in the dept table and 5 million rows in the emp table using this script and measured the second serial execution of a CREATE TABLE AS SELECT statement for each of the 8 described approaches against my 11.2.0.3 instance. The following figure summarizes the results.

Conclusion

A lot of things have changed in the Oracle Database area since Oracle7, even in the niche of string aggregation. I recommend to use LISTAGG (8) whenever possible and avoid the use of SYS_XMLAGG (4) or XSLT (7) for string aggregation. The Collection Type (2) approach is a good alternative if you do not mind creating helper objects otherwise use XMLAGG (6).