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

最新推荐文章于 2024-07-31 14:31:55 发布

xufengzhu

最新推荐文章于 2024-07-31 14:31:55 发布

阅读量4k

点赞数

分类专栏：数据库知识

本文链接：https://blog.csdn.net/xufengzhu/article/details/64127316

版权

数据库知识专栏收录该内容

13 篇文章 1 订阅

订阅专栏

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 = 10
GROUP BY WH_CODE,CUST_GOODS_CODE,CDCM_NAME_CN,CDPA_FORMAT,CDPA_DESC
ORDER BY WH_CODE,CUST_GOODS_CODE

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