The Oracle string connection aggregate function can be used for groupby

1. Create typestrcat_type -- the essence of defining a type aggregate function is an object createorreplacetypestrcat_typeasobject (cat

1. create type strcat_type -- the essence of defining a type aggregate function is an object create or replace type strcat_type as object (cat

1. Create type strcat_type

-- The essence of defining a type aggregate function is an object.
Create or replace type strcat_type as object (
Cat_string varchar2 (4000 ),
-- Object initialization
Static function odciaggresponinitialize (cs_ctx In Out strcat_type)
Return number,
-- Iterative Method of Aggregate functions (this is the most important method)
Member function ODCIAggregateIterate (self In Out strcat_type, value in varchar2)
Return number,
-- This method is used only when the query statement runs in parallel. You can aggregate the query results that run in parallel.
Member function ODCIAggregateMerge (self In Out strcat_type, ctx2 In Out strcat_type)
Return number,
-- Terminate the processing of the aggregate function and return the processing result of the aggregate function.
Member function odciaggresponterminate (self In Out strcat_type, returnValue Out varchar2, flags in number)
Return number
)

2. Create a type body strcat_type
Create or replace type body strcat_type is
Static function odciaggresponinitialize (cs_ctx in out strcat_type) return number
Is
Begin
Cs_ctx: = strcat_type (null );
Return ODCIConst. Success;
End;
Member function ODCIAggregateIterate (self in out strcat_type,
Value IN varchar2)
Return number
Is
Begin
/* The string is ',' is separated */
Self. cat_string: = self. cat_string | ',' | value;
Return ODCIConst. Success;
End;
Member function odciaggresponterminate (self IN Out strcat_type,
ReturnValue OUT varchar2,
Flags IN number)
Return number
Is
Begin
/* Remove null (is null )*/
ReturnValue: = ltrim (rtrim (self. cat_string ,','),',');
Return ODCIConst. Success;
End;
Member function ODCIAggregateMerge (self in out strcat_type,
Ctx2 IN Out strcat_type)
Return number
Is
Begin
Self. cat_string: = self. cat_string | ',' | ctx2.cat _ string;
Return ODCIConst. Success;
End;
End;

3. create function func_strcat

Create or replace function func_strcat (input varchar2)
RETURN varchar2 -- RETURN Value
PARALLEL_ENABLE aggregate using strcat_type; -- make parallel accumulation

------ The above function is created from the network --------
4. Results
Select * from t_test t;

Id keyword synonyms
1 coffee shop
2 coffee shops
3 coffee shops
4. Music
5. Classical Music
6. live a quiet life
7. a comfortable life
_________________________________________
Select t. keyword, func_strcat (t. synonyms)
From t_test t group by t. keyword;

Keyword func_strcat (t. synonyms)
Coffee shop, coffee house, coffee shop
A quiet and comfortable life
Popular music and classical music

,