Oracle's Wm_concat function implements row to column

Have the following data

Deptname	Phone	Username	Isboss
Department A	Phone 1	Employee A	0
Department A	Phone 1	Leader A	1
Department b	Phone 2	Employee B	0
Department b	Phone 2	Employee C	0
Department b	Phone 2	Leader D	1

Need to show effect

Deptname	Phone	Boss	Emp
Department A	Phone 1	Leader A	Employee A
Department b	Phone 2	Leader D	Employee B, Employee C

Because it is the reason for oracle10g, so the Wm_concat function is used, but there is a limit to the length of the wm_concat result, exceeding the ORA-22922: The LOB value that does not exist , this is something, the code is pasted first

SelectT5.deptname,Max(T5.tel), Dbms_lob.substr (Wmsys.wm_concat (To_char (T5.boss))) boss, Dbms_lob.substr (Wmsys.wm_concat (To_Char (T5.notboss))) Notboss from(Selectt1.name Deptname, T1.tel TEL, CaseT2. Isboss when 1  ThenT2. True_nameEndboss, CaseT2. Isboss when 0  ThenT2. True_nameEndNotboss fromt_duty T Left Joint_department T1 ont.dep_id=t1. dep_id Left Joint_contacts T2 ont.contacts_id=T2. contacts_idWhereT.duty_time=Trunc (sysdate)) T5Group  byT5.deptname

The same situation if the ORACLE11GR2 version can be used Listagg function, it is said to solve the above error problem, this needs to be tested later

But I am now 10g, and will report the above error, some of the online solutions have not been solved, someone wrote a custom function, the perfect solution to the test

The original source has not been found, it is not affixed to the link, but still very grateful to solve the problem

Create or ReplaceTYPE Zh_concat_imauthidCurrent_User  asOBJECT (Curr_strVARCHAR2(32767), STATICFUNCTIONOdciaggregateinitialize (sctxinchOut Zh_concat_im)RETURN  Number, MEMBERFUNCTIONOdciaggregateiterate (selfinchOut Zh_concat_im, P1inch VARCHAR2)RETURN  Number, MEMBERFUNCTIONOdciaggregateterminate (selfinchZh_concat_im, returnvalue outVARCHAR2, FLAGSinch  Number)                     RETURN  Number, MEMBERFUNCTIONOdciaggregatemerge (selfinchOut Zh_concat_im, SCTX2inchZH_CONCAT_IM)RETURN  Number);/Create or ReplaceTYPE BODY Zh_concat_im isSTATICFUNCTIONOdciaggregateinitialize (sctxinchOut Zh_concat_im)RETURN  Number   is  BEGINSctx:=Zh_concat_im (NULL) ; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregateiterate (selfinchOut Zh_concat_im, P1inch VARCHAR2)  RETURN  Number   is  BEGIN    IF(Curr_str is  not NULL) ThenCurr_str:=Curr_str|| ':' ||P1; ELSECurr_str:=P1; END IF; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregateterminate (selfinchZh_concat_im, returnvalue outVARCHAR2, FLAGSinch  Number)    RETURN  Number   is  BEGINreturnvalue:=Curr_str; RETURNOdciconst.  SUCCESS; END; MEMBERFUNCTIONOdciaggregatemerge (selfinchOut Zh_concat_im, SCTX2inchzh_concat_im)RETURN  Number   is  BEGIN    IF(SCTX2. Curr_str is  not NULL) ThenSelf . CURR_STR:=Self. Curr_str|| ':' ||SCTX2.    CURR_STR; END IF; RETURNOdciconst.  SUCCESS; END;END;/Create or Replace FUNCTIONZh_concat (P1VARCHAR2)RETURN VARCHAR2AGGREGATE USING zh_concat_im;/

Oracle's Wm_concat function implements row to column