{"id":207,"date":"2012-12-23T00:21:10","date_gmt":"2012-12-22T23:21:10","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=207"},"modified":"2023-11-08T19:59:11","modified_gmt":"2023-11-08T18:59:11","slug":"building-comma-separated-values-with-oracle-sql","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/","title":{"rendered":"Building Comma Separated Values with Oracle & SQL"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t
DEPTNO<\/th>DNAME<\/th>ENAME_LIST<\/th>\n<\/tr>\n<\/thead>\n
10<\/td>ACCOUNTING<\/td>CLARK, KING, MILLER<\/td>\n<\/tr>\n
20<\/td>RESEARCH<\/td>ADAMS, FORD, JONES, SCOTT, SMITH<\/td>\n<\/tr>\n
30<\/td>SALES<\/td>ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD<\/td>\n<\/tr>\n
40<\/td>OPERATIONS<\/td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n

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.<\/p>\n\n\n\n

I cover just some options in this post, if you are interested in more then please visit Tim Hall’s String Aggregation Techniques<\/a> on oracle-base.com<\/a>.<\/p>\n\n\n\n

Oracle7<\/h3>\n\n\n\n

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 then something like the following was necessary to build comma-separated values:
<\/a><\/p>\n\n\n\n

1) Oracle7 UDF Style<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> FUNCTION deptno_to_ename_list(in_deptno <\/span>IN<\/span> <\/span>VARCHAR2<\/span>)<\/span><\/span>\n   <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> <\/span>IS<\/span><\/span>\n   <\/span>CURSOR<\/span> <\/span>l_cur<\/span> <\/span>IS<\/span><\/span>\n      <\/span>SELECT<\/span> ename<\/span><\/span>\n        <\/span>FROM<\/span> emp<\/span><\/span>\n       <\/span>WHERE<\/span> deptno = in_deptno<\/span><\/span>\n       <\/span>ORDER BY<\/span> ename;<\/span><\/span>\n   <\/span>l_ret<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>FOR<\/span> <\/span>l_rec<\/span> <\/span>IN<\/span> <\/span>l_cur<\/span><\/span>\n   <\/span>LOOP<\/span><\/span>\n      <\/span>IF<\/span> <\/span>l_cur<\/span>%ROWCOUNT<\/span> > <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n         <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || <\/span>', '<\/span>;<\/span><\/span>\n      <\/span>END IF<\/span>;<\/span><\/span>\n      <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || <\/span>l_rec<\/span>.ename;<\/span><\/span>\n   <\/span>END LOOP<\/span>;<\/span><\/span>\n   <\/span>RETURN<\/span> <\/span>l_ret<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> deptno, dname, deptno_to_ename_list(deptno) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept<\/span><\/span>\n <\/span>ORDER BY<\/span> deptno;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Oracle8<\/h3>\n\n\n\n
Version 8.0 came with the object option allowing to solve the problem in more generic ways.
<\/a><\/p>\n\n\n\n
2) Oracle8 Collection Type Style<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>TYPE<\/span> string_tabtype <\/span>IS<\/span> <\/span>TABLE<\/span> <\/span>OF<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> FUNCTION collection_to_comma_list(<\/span><\/span>\n   in_strings <\/span>IN<\/span> string_tabtype<\/span><\/span>\n) <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> <\/span>IS<\/span><\/span>\n   <\/span>l_ret<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>IF<\/span> in_strings<\/span>.COUNT<\/span> > <\/span>0<\/span> <\/span>THEN<\/span><\/span>\n      <\/span>FOR<\/span> i <\/span>IN<\/span> <\/span>1<\/span> .. in_strings<\/span>.COUNT<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         <\/span>IF<\/span> i > <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n            <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || <\/span>', '<\/span>;<\/span><\/span>\n         <\/span>END IF<\/span>;<\/span><\/span>\n         <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || in_strings(i);<\/span><\/span>\n      <\/span>END LOOP<\/span>;<\/span><\/span>\n   <\/span>END IF<\/span>;<\/span><\/span>\n   <\/span>RETURN<\/span> <\/span>l_ret<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       collection_to_comma_list(<\/span><\/span>\n          <\/span>CAST<\/span>(<\/span><\/span>\n             MULTISET(<\/span><\/span>\n                <\/span>SELECT<\/span> ename<\/span><\/span>\n                  <\/span>FROM<\/span> emp e<\/span><\/span>\n                 <\/span>WHERE<\/span> e.deptno = d.deptno<\/span><\/span>\n                 <\/span>ORDER BY<\/span> ename<\/span><\/span>\n             ) <\/span>AS<\/span> string_tabtype<\/span><\/span>\n          )<\/span><\/span>\n       ) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
3) Oracle8 RefCursor Style<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>mytypes_pkg<\/span> <\/span>IS<\/span><\/span>\n   <\/span>TYPE<\/span> refcursor_type <\/span>IS<\/span> REF <\/span>CURSOR<\/span>; <\/span><\/span>\nEND<\/span> mytypes_pkg;<\/span><\/span>\n\/ <\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> FUNCTION cursor_to_comma_list(<\/span><\/span>\n   in_refcursor <\/span>IN<\/span> <\/span>mytypes_pkg.<\/span>refcursor_type<\/span><\/span>\n) <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> <\/span>IS<\/span><\/span>\n   <\/span>l_string<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\n   <\/span>l_ret<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>LOOP<\/span><\/span>\n      <\/span>FETCH<\/span> in_refcursor <\/span>INTO<\/span> <\/span>l_string<\/span>;<\/span><\/span>\n      <\/span>EXIT<\/span> <\/span>WHEN<\/span> in_refcursor<\/span>%NOTFOUND<\/span>;<\/span><\/span>\n      <\/span>IF<\/span> in_refcursor<\/span>%ROWCOUNT<\/span> > <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n         <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || <\/span>', '<\/span>;<\/span><\/span>\n      <\/span>END IF<\/span>;<\/span><\/span>\n      <\/span>l_ret<\/span> := <\/span>l_ret<\/span> || <\/span>l_string<\/span>;<\/span><\/span>\n   <\/span>END LOOP<\/span>;<\/span><\/span>\n   <\/span>CLOSE<\/span> in_refcursor;<\/span><\/span>\n   <\/span>RETURN<\/span> <\/span>l_ret<\/span>;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       cursor_to_comma_list(<\/span><\/span>\n          <\/span>CURSOR<\/span>(<\/span><\/span>\n             <\/span>SELECT<\/span> ename<\/span><\/span>\n               <\/span>FROM<\/span> emp e<\/span><\/span>\n              <\/span>WHERE<\/span> e.deptno = d.deptno<\/span><\/span>\n              <\/span>ORDER BY<\/span> ename<\/span><\/span>\n          ) <\/span><\/span>\n       ) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Oracle9i<\/em> Release 1<\/h3>\n\n\n\n
Version 9.0 came with basic XML support which allowed to aggregate strings without the need for a helper function.
<\/a><\/p>\n\n\n\n
4) Oracle9iR1 SYS_XMLAGG\/String Style<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       <\/span>RTRIM<\/span>(<\/span><\/span>\n          SYS_XMLAGG(<\/span><\/span>\n             SYS_XMLGEN(<\/span><\/span>\n                e.ename||<\/span>', '<\/span><\/span>\n             )<\/span><\/span>\n          ).EXTRACT(<\/span><\/span>\n             <\/span>'\/ROWSET\/ROW\/text()'<\/span><\/span>\n          ).getStringVal(),<\/span><\/span>\n          <\/span>', '<\/span><\/span>\n       ) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> emp e <\/span>ON<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>GROUP BY<\/span> d.deptno, d.dname<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
5) Oracle9iR1 Aggregate Function Style<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>TYPE<\/span> string_tabtype <\/span>AS<\/span> <\/span>TABLE<\/span> <\/span>OF<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>TYPE<\/span> mylistagg_type <\/span>AS<\/span> OBJECT (<\/span><\/span>\n  strings string_tabtype,<\/span><\/span>\n  STATIC FUNCTION ODCIAggregateInitialize(<\/span><\/span>\n     sctx        <\/span>IN<\/span> <\/span>OUT<\/span> mylistagg_type<\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span>,<\/span><\/span>\n  MEMBER FUNCTION ODCIAggregateIterate(<\/span><\/span>\n     SELF        <\/span>IN<\/span> <\/span>OUT<\/span> mylistagg_type,<\/span><\/span>\n     value       <\/span>IN<\/span>     <\/span>VARCHAR2<\/span> <\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span>,<\/span><\/span>\n  MEMBER FUNCTION ODCIAggregateTerminate(<\/span><\/span>\n     SELF        <\/span>IN<\/span>     mylistagg_type,<\/span><\/span>\n     returnValue <\/span>OUT<\/span>    <\/span>VARCHAR2<\/span>,<\/span><\/span>\n     flags       <\/span>IN<\/span>     <\/span>NUMBER<\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span>,<\/span><\/span>\n  MEMBER FUNCTION ODCIAggregateMerge(<\/span><\/span>\n     SELF        <\/span>IN<\/span> <\/span>OUT<\/span>  mylistagg_type,<\/span><\/span>\n     ctx2        <\/span>IN<\/span>      mylistagg_type)<\/span><\/span>\n    <\/span>RETURN<\/span> <\/span>NUMBER<\/span><\/span>\n);<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> <\/span>TYPE<\/span> BODY mylistagg_type <\/span>IS<\/span><\/span>\n  STATIC FUNCTION ODCIAggregateInitialize(<\/span><\/span>\n     sctx        <\/span>IN<\/span> <\/span>OUT<\/span> mylistagg_type<\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span> <\/span>IS<\/span><\/span>\n  <\/span>BEGIN<\/span><\/span>\n    sctx := mylistagg_type(string_tabtype());<\/span><\/span>\n    <\/span>RETURN<\/span> ODCIConst.Success;<\/span><\/span>\n  <\/span>END<\/span>;<\/span><\/span>\n<\/span>\n  MEMBER FUNCTION ODCIAggregateIterate(<\/span><\/span>\n     SELF        <\/span>IN<\/span> <\/span>OUT<\/span> mylistagg_type,<\/span><\/span>\n     value       <\/span>IN<\/span>     <\/span>VARCHAR2<\/span> <\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span> <\/span>IS<\/span><\/span>\n  <\/span>BEGIN<\/span><\/span>\n    SELF.strings<\/span>.EXTEND<\/span>;<\/span><\/span>\n    SELF.strings(SELF.strings<\/span>.COUNT<\/span>) := VALUE;<\/span><\/span>\n    <\/span>RETURN<\/span> ODCIConst.Success;<\/span><\/span>\n  <\/span>END<\/span> ODCIAggregateIterate;<\/span><\/span>\n<\/span>\n  MEMBER FUNCTION ODCIAggregateTerminate(<\/span><\/span>\n     SELF        <\/span>IN<\/span>     mylistagg_type,<\/span><\/span>\n     returnValue <\/span>OUT<\/span>    <\/span>VARCHAR2<\/span>,<\/span><\/span>\n     flags       <\/span>IN<\/span>     <\/span>NUMBER<\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span> <\/span>IS<\/span><\/span>\n     <\/span>l_sorted_strings<\/span> string_tabtype;<\/span><\/span>\n     <\/span>l_return_value<\/span> <\/span>VARCHAR2<\/span>(<\/span>2000<\/span>);<\/span><\/span>\n  <\/span>BEGIN<\/span><\/span>\n    <\/span>SELECT<\/span> COLUMN_VALUE <\/span><\/span>\n      <\/span>BULK COLLECT<\/span> <\/span>INTO<\/span> <\/span>l_sorted_strings<\/span> <\/span><\/span>\n      <\/span>FROM<\/span> <\/span>TABLE<\/span>(strings) <\/span><\/span>\n     <\/span>ORDER BY<\/span> COLUMN_VALUE;<\/span><\/span>\n    <\/span>FOR<\/span> i <\/span>IN<\/span> <\/span>1<\/span> .. <\/span>l_sorted_strings<\/span>.COUNT<\/span><\/span>\n    <\/span>LOOP<\/span><\/span>\n       <\/span>IF<\/span> i > <\/span>1<\/span> <\/span>THEN<\/span><\/span>\n          <\/span>l_return_value<\/span> := <\/span>l_return_value<\/span> || <\/span>', '<\/span>;<\/span><\/span>\n       <\/span>END IF<\/span>;<\/span><\/span>\n       <\/span>l_return_value<\/span> := <\/span>l_return_value<\/span> || <\/span>l_sorted_strings<\/span>(i);<\/span><\/span>\n    <\/span>END LOOP<\/span>;<\/span><\/span>\n    returnValue := <\/span>l_return_value<\/span>;<\/span><\/span>\n    <\/span>RETURN<\/span> ODCIConst.Success;  <\/span><\/span>\n  <\/span>END<\/span> ODCIAggregateTerminate;<\/span><\/span>\n<\/span>\n  MEMBER FUNCTION ODCIAggregateMerge(<\/span><\/span>\n     SELF        <\/span>IN<\/span> <\/span>OUT<\/span>  mylistagg_type,<\/span><\/span>\n     ctx2        <\/span>IN<\/span>      mylistagg_type<\/span><\/span>\n  ) <\/span>RETURN<\/span> <\/span>NUMBER<\/span> <\/span>IS<\/span><\/span>\n  <\/span>BEGIN<\/span><\/span>\n    <\/span>FOR<\/span> i <\/span>IN<\/span> <\/span>1<\/span> .. ctx2.strings<\/span>.COUNT<\/span> <\/span><\/span>\n    <\/span>LOOP<\/span><\/span>\n       SELF.strings<\/span>.EXTEND<\/span>;<\/span><\/span>\n       SELF.strings(SELF.strings<\/span>.COUNT<\/span>) := ctx2.strings(i);<\/span><\/span>\n    <\/span>END LOOP<\/span>;<\/span><\/span>\n    <\/span>RETURN<\/span> ODCIConst.Success;<\/span><\/span>\n  <\/span>END<\/span> ODCIAggregateMerge;<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nCREATE OR REPLACE<\/span> FUNCTION mylistagg (<\/span><\/span>\n   in_string <\/span>IN<\/span> <\/span>VARCHAR2<\/span><\/span>\n) <\/span>RETURN<\/span> <\/span>VARCHAR2<\/span> PARALLEL_ENABLE AGGREGATE <\/span>USING<\/span> mylistagg_type;<\/span><\/span>\n\/<\/span><\/span>\n<\/span>\nSELECT<\/span> d.deptno, d.dname, mylistagg(e.ename) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> emp e <\/span>ON<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>GROUP BY<\/span> d.deptno, d.dname<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Oracle9i<\/em> Release 2<\/h3>\n\n\n\n
Version 9i Release 2 came with SQL\/XML support and the function XMLAGG which replaces SYS_XMLAGG and allows sorting the elements to be aggregated (see line 7).
<\/a><\/p>\n\n\n\n
6) Oracle9iR2 XMLAGG\/String Style<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       <\/span>RTRIM<\/span>(<\/span><\/span>\n          <\/span>XMLAGG<\/span>(<\/span><\/span>\n             <\/span>XMLELEMENT<\/span>(<\/span><\/span>\n                <\/span>"e"<\/span>, e.ename || <\/span>', '<\/span><\/span>\n             ) <\/span>ORDER BY<\/span> e.ename<\/span><\/span>\n          ).<\/span>EXTRACT<\/span>(<\/span><\/span>\n             <\/span>'\/e\/text()'<\/span><\/span>\n          ).getStringVal(), <\/span>', '<\/span><\/span>\n       ) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> emp e <\/span>ON<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>GROUP BY<\/span> d.deptno, d.dname<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
7) Oracle9iR2 XMLAGG\/XSLT Style<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       XMLTRANSFORM(<\/span><\/span>\n          XMLAGG(<\/span><\/span>\n             XMLELEMENT(<\/span><\/span>\n                <\/span>"e"<\/span>, e.ename<\/span><\/span>\n             ) <\/span>ORDER BY<\/span> e.ename<\/span><\/span>\n          ), <\/span>'<xsl:stylesheet version="1.0" xmlns:xsl="http:\/\/www.w3.org\/1999\/XSL\/Transform"><\/span><\/span>\n                 <xsl:output method="text"\/><\/span><\/span>\n                 <xsl:template match="\/"><\/span><\/span>\n                    <xsl:for-each select="*"><\/span><\/span>\n                       <xsl:if test="position() != 1"><\/span><\/span>\n                          <xsl:value-of select="'', ''"\/><\/span><\/span>\n                       <\/xsl:if><\/span><\/span>\n                       <xsl:value-of select="."\/><\/span><\/span>\n                    <\/xsl:for-each><\/span><\/span>\n                 <\/xsl:template><\/span><\/span>\n              <\/xsl:stylesheet>'<\/span><\/span>\n       ).getStringVal() <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> emp e <\/span>ON<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>GROUP BY<\/span> d.deptno, d.dname<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Oracle Database 11g<\/em> Release 2<\/h3>\n\n\n\n
As mentioned at the beginning, in version 11g Release 2 Oracle finally introduced the aggregate function LISTAGG  to conveniently aggregate strings.
<\/a><\/p>\n\n\n\n
8) Oracle 11gR2 ListAgg Style<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> d.deptno,<\/span><\/span>\n       d.dname,<\/span><\/span>\n       LISTAGG (<\/span><\/span>\n          e.ename, <\/span>', '<\/span><\/span>\n       ) <\/span>WITHIN<\/span> <\/span>GROUP<\/span> (<\/span><\/span>\n          <\/span>ORDER BY<\/span> e.ename<\/span><\/span>\n       ) <\/span>AS<\/span> ename_list<\/span><\/span>\n  <\/span>FROM<\/span> dept d<\/span><\/span>\n  <\/span>LEFT JOIN<\/span> emp e <\/span>ON<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>GROUP BY<\/span> d.deptno, d.dname<\/span><\/span>\n <\/span>ORDER BY<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Performance Comparison<\/h3>\n\n\n\n
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<\/a> 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.<\/p>\n\n\n\n
\"RuntimeCSV\"<\/a><\/figure>\n\n\n\n
Conclusion<\/h3>\n\n\n\n
A lot of things have changed in the Oracle Database area since Oracle7, even in the niche of string aggregation. I recommend using LISTAGG (8<\/a>) whenever possible and avoid the use of SYS_XMLAGG (4<\/a>) or XSLT (7<\/a>) for string aggregation. The Collection Type (2<\/a>) approach is a good alternative if you do not mind creating helper objects otherwise use XMLAGG (6<\/a>).<\/p>\n","protected":false},"excerpt":{"rendered":"
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. Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":768,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[27,25,13,85],"class_list":["post-207","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-csv","tag-performance","tag-plsql","tag-sql"],"yoast_head":"\nBuilding Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Building Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"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. Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-22T23:21:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T18:59:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"768\" \/>\n\t<meta property=\"og:image:height\" content=\"605\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Building Comma Separated Values with Oracle & SQL\",\"datePublished\":\"2012-12-22T23:21:10+00:00\",\"dateModified\":\"2023-11-08T18:59:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/\"},\"wordCount\":545,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/RuntimeCSV1.png\",\"keywords\":[\"CSV\",\"Performance\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/\",\"name\":\"Building Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/RuntimeCSV1.png\",\"datePublished\":\"2012-12-22T23:21:10+00:00\",\"dateModified\":\"2023-11-08T18:59:11+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/RuntimeCSV1.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/RuntimeCSV1.png\",\"width\":768,\"height\":605},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2012\\\/12\\\/23\\\/building-comma-separated-values-with-oracle-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Building Comma Separated Values with Oracle & SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Building Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/","og_locale":"en_US","og_type":"article","og_title":"Building Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog","og_description":"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. Oracle introduced the aggregate function LISTAGG for that purpose in 11.2. If you [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2012-12-22T23:21:10+00:00","article_modified_time":"2023-11-08T18:59:11+00:00","og_image":[{"width":768,"height":605,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Building Comma Separated Values with Oracle & SQL","datePublished":"2012-12-22T23:21:10+00:00","dateModified":"2023-11-08T18:59:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/"},"wordCount":545,"commentCount":2,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png","keywords":["CSV","Performance","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/","url":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/","name":"Building Comma Separated Values with Oracle & SQL - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png","datePublished":"2012-12-22T23:21:10+00:00","dateModified":"2023-11-08T18:59:11+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2012\/12\/RuntimeCSV1.png","width":768,"height":605},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2012\/12\/23\/building-comma-separated-values-with-oracle-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Building Comma Separated Values with Oracle & SQL"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=207"}],"version-history":[{"count":9,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/207\/revisions"}],"predecessor-version":[{"id":12747,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/207\/revisions\/12747"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/768"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
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 an XSLT example:
<\/a><\/p>\n\n\n\n
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 to sort the result.
<\/a><\/p>\n\n\n\n
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 then. BTW: SYS_REFCURSOR was introduced in 9.0, so this specific PL\/SQL type is really necessary with version 8.0.
<\/a><\/p>\n\n\n\n