首页 > 代码库 > oracle列转行 WM_CONCAT LISTAGG

oracle列转行 WM_CONCAT LISTAGG

开发给个SQL说给某个条件时报ORA-22922

技术分享

 

 代码段:

1 SELECT 袋号,
2                     SUM(实际重量) AS 实际重量,
3                     SUM(材积重量) AS 材积重量,
4                     COUNT(运单号) AS 件数,
5                     TO_CHAR(WMSYS.WM_CONCAT(运单号)) AS 运单编号
6                FROM TBL
7               GROUP BY 袋号

 

修改成如下后解决:

1 SELECT 袋号,
2        SUM(实际重量) AS 实际重量,
3        SUM(材积重量) AS 材积重量,
4        COUNT(运单号) AS 件数,
5        LISTAGG(运单号, ,) WITHIN GROUP(ORDER BY 运单号) AS 运单编号
6   FROM TBL
7  GROUP BY 袋号

注:

LISTAGG为11G2才提供的函数

oracle列转行 WM_CONCAT LISTAGG