Oracle 同一列的字符串值相加-列转行函数 Listagg()

Oracle函数 LISTAGG()

这是一个Oracle的列值转化为行值函数。

基础用法

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)  

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。非常方便。

实际用例

SELECT LISTAGG(EDI_TRANS_ID,'&') WITHIN GROUP(ORDER BY EDI_TRANS_ID) EDI_TRANS_ID,       TO_CHAR(WH_CODE) WH_CODE,       TO_CHAR(CUST_GOODS_CODE) CUST_GOODS_CODE,       TO_CHAR(CDCM_NAME_CN) CDCM_NAME_CN,       TO_CHAR(SUM(QTY)) QTY,       TO_CHAR(CDPA_FORMAT) CDPA_FORMAT,       TO_CHAR(CDPA_DESC) CDPA_DESC  FROM EDI.V_WH_INVENTORY WHERE     RECEIVER = 'DAVCO'       AND CREATE_TIME > SYSDATE - 7    AND WH_CODE = '502.5015'    AND TO_CHAR(CREATE_TIME,'YYYYMMDD')='20170320'       AND EDIFLAG = 10GROUP BY WH_CODE,CUST_GOODS_CODE,CDCM_NAME_CN,CDPA_FORMAT,CDPA_DESCORDER BY WH_CODE,CUST_GOODS_CODE

该SQL是通过分组将EDI_TRANS_ID的值变成行值

查询结果