There is no group concatenation like in MySQL. If you want a group concatenation aggregate function, you have to write your own. Here is my implementation:
drop type T_GROUP_CONCAT;
create or replace type GROUP_CONCAT_PARAM as object
(
val varchar2(255),
separator varchar2(10),
numToConcat NUMBER,
MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt return VARCHAR2
);
--map function needed for disctinct in select clauses
CREATE OR REPLACE TYPE BODY GROUP_CONCAT_PARAM IS
MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt return VARCHAR2 is
begin
return val;
end;
end;
/
CREATE OR REPLACE TYPE T_GROUP_CONCAT
AS OBJECT (
runningConcat VARCHAR2(5000),
runningCount NUMBER,
STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT T_GROUP_CONCAT
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT T_GROUP_CONCAT,
val IN GROUP_CONCAT_PARAM
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate
( self IN T_GROUP_CONCAT,
returnValue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT T_GROUP_CONCAT,
ctx2 IN T_GROUP_CONCAT
) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT AS
STATIC FUNCTION ODCIAggregateInitialize
( actx IN OUT T_GROUP_CONCAT
) RETURN NUMBER IS
BEGIN
IF actx IS NULL THEN
actx := T_GROUP_CONCAT ('', 0);
ELSE
actx.runningConcat := '';
actx.runningCount := 0;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate
( self IN OUT T_GROUP_CONCAT,
val IN GROUP_CONCAT_PARAM
) RETURN NUMBER IS
BEGIN
if self.runningCount = 0 then
self.runningConcat := val.val;
elsif self.runningCount < val.numToConcat then
self.runningConcat := self.runningConcat || val.separator || val.val;
end if;
self.runningCount := self.runningCount + 1;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate
( self IN T_GROUP_CONCAT,
ReturnValue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnValue := self.runningConcat;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge
(self IN OUT T_GROUP_CONCAT,
ctx2 IN T_GROUP_CONCAT
) RETURN NUMBER IS
BEGIN
self.runningConcat := self.runningConcat || ',' || ctx2.runningConcat;
self.runningCount := self.runningCount + ctx2.runningCount;
RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION GROUP_CONCAT
( x GROUP_CONCAT_PARAM
) RETURN VARCHAR2
--PARALLEL_ENABLE
AGGREGATE USING T_GROUP_CONCAT;
/
To use it:
select GROUP_CONCAT(GROUP_CONCAT_PARAM(tbl.someColumn, '|', 2)) from someTable tbl