This project is read-only.
How to concatenate multiple rows of strings in one string.

For example, the following query returns several rows:
SELECT table_name FROM user_tables WHERE table_name LIKE 'A%';

TABLE_NAME
---------------------
ADDRESS
ADS
APPLICATION

And you need to convert them into one string:
ADDRESS, ADS, APPLICATION


Here there are 6 different methods of how to do this:
---------------------------------------------------------------------------------
-- 1.
-- Function LISTAGG. Available from 11.2
-- Result is varchar2, therefore if result length > 4000 - then raises an exception
SELECT LISTAGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name)
	FROM user_tables
	WHERE table_name LIKE 'A%';

SELECT SUBSTR(table_name, 1, 1) AS start_with,
              LISTAGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name) AS tables
  FROM user_tables
GROUP BY SUBSTR(table_name, 1, 1);

---------------------------------------------------------------------------------
-- 2.
-- Function wmsys.wm_concat. Undocumented and unsupported!
SELECT wmsys.wm_concat(table_name) FROM user_tables WHERE table_name LIKE 'A%';

---------------------------------------------------------------------------------
-- 3.
-- sys_xmlagg
-- Uses XML, therefore all special symbols will be replaced (like & -> &)
SELECT RTRIM(dbms_lob.substr(sys_xmlagg(xmlelement(col, t.table_name || ', ')).extract('/ROWSET/COL/text()').getclobval(), 4000), ', ')
FROM user_tables t
WHERE t.table_name LIKE 'A%';

---------------------------------------------------------------------------------
-- 4.
-- sys.stragg. Undocumented and unsupported!
SELECT RTRIM(sys.stragg(table_name || ', '), ', ') FROM user_tables WHERE table_name LIKE 'A%';

---------------------------------------------------------------------------------
-- 5.
-- Create your own aggregate function
SELECT RTRIM(aggr_string_join(DISTINCT table_name || ', '), ', ')
FROM user_tables WHERE table_name LIKE 'A%';

-- Code:
CREATE OR REPLACE TYPE aggr_string_join_t AS OBJECT
(
  result	VARCHAR2(4000),
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT aggr_string_join_t) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT aggr_string_join_t, psValue IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT aggr_string_join_t, ctx2 IN aggr_string_join_t) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate
  (
  	self IN OUT aggr_string_join_t,
    psReturnValue OUT VARCHAR2,
    pnFlag IN NUMBER
   )
   RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY aggr_string_join_t
IS
	STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT aggr_string_join_t) RETURN NUMBER
	IS
	BEGIN
		sctx := aggr_string_join_t('');
		RETURN ODCIConst.Success;
	END;
 	
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT aggr_string_join_t, psValue IN VARCHAR2) RETURN NUMBER
  IS
  BEGIN
  	IF NVL(LENGTH(self.result || psValue), 0) <= 4000 THEN
  		self.result := self.result || psValue;
  	END IF;
  	RETURN ODCIConst.Success;
  END;
  
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT aggr_string_join_t, ctx2 IN aggr_string_join_t) RETURN NUMBER
  IS
  BEGIN
  	IF NVL(LENGTH(self.result || ctx2.result), 0) <= 4000 THEN
  		self.result := self.result || ctx2.result;
  	END IF;
  	RETURN ODCIConst.Success;
  END;
  
  MEMBER FUNCTION ODCIAggregateTerminate
  (
  	self IN OUT aggr_string_join_t,
    psReturnValue OUT VARCHAR2,
    pnFlag IN NUMBER
  )
  RETURN NUMBER
  IS
  BEGIN
  	psReturnValue := self.result;
  	RETURN ODCIConst.Success;
  END;

END;

CREATE OR REPLACE FUNCTION aggr_string_join(psText IN VARCHAR2)
	RETURN VARCHAR2
	DETERMINISTIC
	PARALLEL_ENABLE
	AGGREGATE USING aggr_string_join_t;

---------------------------------------------------------------------------------
-- 6.
-- Custom function using SYS_REFCURSOR
-- It opens cursors, therefore if apply for more than 1000 records - it will run out of available cursors
SELECT string_join(CURSOR(SELECT table_name FROM user_tables WHERE table_name LIKE 'A%' ORDER BY table_name)) FROM dual;


-- Code:
CREATE OR REPLACE FUNCTION string_join
(
	pCursor SYS_REFCURSOR,
  psSeparator VARCHAR2 := ', ',
  pnMaxLength PLS_INTEGER := 4000
)
RETURN VARCHAR2
IS
	sToken VARCHAR2(4000);
	sResult VARCHAR2(32767);
	nMaxLength PLS_INTEGER;
	nSepLength PLS_INTEGER;
BEGIN
	nMaxLength := TRUNC(pnMaxLength);
	IF nMaxLength < 1 OR nMaxLength > 4000 THEN
		nMaxLength := 4000;
	END IF;
	
	nSepLength := NVL(LENGTH(psSeparator), 0);
	
	LOOP
		FETCH pCursor INTO sToken;
    EXIT WHEN pCursor%NOTFOUND;
		IF sToken IS NOT NULL THEN
			sToken := sToken || psSeparator;
			
			IF ( NVL(LENGTH(sResult), 0) + NVL(LENGTH(sToken), 0) ) <= pnMaxLength THEN
				sResult := sResult || sToken;
			END IF;
		END IF;
	END LOOP;
	
	IF nSepLength > 0 THEN
		IF NVL(LENGTH(sResult), 0) > nSepLength THEN
			sResult := SUBSTR(sResult, 1, LENGTH(sResult) - nSepLength);
		END IF;
	END IF;
	
	RETURN sResult;
END string_join;


tags: sql, pl/sql, oracle, concatenate

Last edited Jul 11, 2012 at 6:28 AM by alekseiv, version 3

Comments

No comments yet.