oracle的wm_concat函數實現行轉列

標籤：from   完美解決   str   11gr2   cti   case   center   bms   連結   

有以下資料

deptname	phone	username	isboss
部門A	電話1	員工A	0
部門A	電話1	領導A	1
部門B	電話2	員工B	0
部門B	電話2	員工C	0
部門B	電話2	領導D	1

 

需要展示效果

deptname	phone	boss	emp
部門A	電話1	領導A	員工A
部門B	電話2	領導D	員工B,員工C

因為是oracle10g的原因,所以使用了wm_concat函數,但對wm_concat結果長度有限制,超出會報ORA-22922:不存在的LOB值,這是後話,先貼代碼

Select t5.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 (Select t1.NAME deptname,               t1.TEL tel,               Case t2.ISBOSS                 When 1 Then                  t2.TRUE_NAME               End boss,               Case t2.ISBOSS                 When 0 Then                  t2.TRUE_NAME               End notboss          From t_duty t          Left Join t_department t1            On t.DEP_ID = t1.DEP_ID          Left Join t_contacts t2            On t.CONTACTS_ID = t2.CONTACTS_ID         Where t.DUTY_TIME = Trunc(SysDate) ) t5 Group By t5.deptname

同樣的情況的如果是oracle11gR2版本可以用LISTAGG函數,據說可以解決上面的報錯問題,這個有待以後測試

但是我現在的是10g的,而且會報上面的錯誤,網上的一些解決方案都沒解決,有人寫了自訂函數,親測完美解決

原出處已經找不到了,就不貼連結了,但還是非常感謝解決了這個問題

create or replace TYPE zh_concat_imAUTHID CURRENT_USER AS OBJECT(  CURR_STR VARCHAR2(32767),  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,               P1 IN VARCHAR2) RETURN NUMBER,  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,                                 RETURNVALUE OUT VARCHAR2,                                 FLAGS IN NUMBER)                     RETURN NUMBER,  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,                    SCTX2 IN  zh_concat_im) RETURN NUMBER);/create or replace TYPE BODY zh_concat_imIS  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)  RETURN NUMBER  IS  BEGIN    SCTX := zh_concat_im(NULL) ;    RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,          P1 IN VARCHAR2)  RETURN NUMBER  IS  BEGIN    IF(CURR_STR IS NOT NULL) THEN      CURR_STR := CURR_STR || ‘:‘ || P1;    ELSE      CURR_STR := P1;    END IF;    RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,                                 RETURNVALUE OUT VARCHAR2,                                 FLAGS IN NUMBER)    RETURN NUMBER  IS  BEGIN    RETURNVALUE := CURR_STR ;    RETURN ODCICONST.SUCCESS;  END;  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,                                   SCTX2 IN zh_concat_im)  RETURN NUMBER  IS  BEGIN    IF(SCTX2.CURR_STR IS NOT NULL) THEN      SELF.CURR_STR := SELF.CURR_STR || ‘:‘ || SCTX2.CURR_STR ;    END IF;    RETURN ODCICONST.SUCCESS;  END;END;/create or replace FUNCTION zh_concat(P1 VARCHAR2)RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;/

 

oracle的wm_concat函數實現行轉列