oracle的 listagg() WITHIN GROUP () 行转列函数的使用

原文链接：https://blog.csdn.net/sinat_36257389/java/article/details/81004843

1.使用条件查询 查询部门为20的员工列表

-- 查询部门为20的员工列表
SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;
    效果：

 

2.使用  listagg() WITHIN GROUP ()  将多行合并成一行(比较常用)

SELECT
T .DEPTNO,
listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
FROM
SCOTT.EMP T
WHERE
T .DEPTNO = '20'
GROUP BY
T .DEPTNO
    效果：

 

3. 使用 listagg() within GROUP () over  将多行记录在一行显示(没有遇到过这种使用场景)

SELECT
T .DEPTNO,
listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) over(PARTITION BY T .DEPTNO)
FROM
SCOTT.EMP T
WHERE
T .DEPTNO = '20'
    效果：

 

 注:使用的表数据是oracle 用户scott下的emp（员工）表。。。

工作例子：

SELECT A.OP_ID,
A.INV_ACC,
A.INV_NAME,
A.BUSI_DEPT_CODE,
A.REG_ORG,
A.TRADE_MKT_CODE,
A.INV_ACC_TYPE,
A.COMBINA_FUND_CODE,
A.INV_ACC_STATUS,
A.OPEN_DATE,
A.CLOSE_DATE,
A.TRUST_SEAT,
A.ASSIGN_STATUS,
A.RELA_INV_ACC,
A.SETTLE_ENTITY_ID,
A.UPDATE_DATE,
A.UPDATE_TIME,
A.NOTE,
A.INC_ACC_ATTRIBUTE,
A.DEFAULT_TRADE_ACC,
A.OP_DEFAULT_TRADE_ACC,
A.ALLOW_MULTI_APPLY,
A.OP_USER,
A.OP_DATE,
A.OP_TIME,
A.OP_TYPE,
A.OP_STATUS,
A.OP_NOTE,
A.CHECK_USER,
A.CHECK_DATE,
A.CHECK_TIME,
A.CHECK_STATUS,
A.CHECK_NOTE
FROM EA_IUOP.ACC_INV_ACC_APPLY A
WHERE A.CHECK_STATUS = '0'
UNION ALL
SELECT K.OP_ID,
K.INV_ACC,
K.INV_NAME,
LISTAGG(K.BUSI_DEPT_CODE, ',') WITHIN GROUP(ORDER BY K.INV_ACC) AS BUSI_DEPT_CODE,
K.REG_ORG,
K.TRADE_MKT_CODE,
K.INV_ACC_TYPE,
K.COMBINA_FUND_CODE,
K.INV_ACC_STATUS,
K.OPEN_DATE,
K.CLOSE_DATE,
K.TRUST_SEAT,
K.ASSIGN_STATUS,
K.RELA_INV_ACC,
K.SETTLE_ENTITY_ID,
K.UPDATE_DATE,
K.UPDATE_TIME,
K.NOTE,
K.INC_ACC_ATTRIBUTE,
K.DEFAULT_TRADE_ACC,
K.OP_DEFAULT_TRADE_ACC,
K.ALLOW_MULTI_APPLY,
K.OP_USER,
K.OP_DATE,
K.OP_TIME,
K.OP_TYPE,
K.OP_STATUS,
K.OP_NOTE,
K.CHECK_USER,
K.CHECK_DATE,
K.CHECK_TIME,
K.CHECK_STATUS,
K.CHECK_NOTE
FROM(SELECT NULL AS OP_ID,
A.INV_ACC,
A.INV_NAME,
C.BUSI_DEPT_CODE,
A.REG_ORG,
A.TRADE_MKT_CODE,
A.INV_ACC_TYPE,
LISTAGG(B.COMBINA_FUND_CODE, ',') WITHIN GROUP(ORDER BY B.COMBINA_FUND_CODE) AS COMBINA_FUND_CODE,
A.INV_ACC_STATUS,
A.OPEN_DATE,
A.CLOSE_DATE,
A.TRUST_SEAT,
A.ASSIGN_STATUS,
A.RELA_INV_ACC,
A.SETTLE_ENTITY_ID,
A.UPDATE_DATE,
A.UPDATE_TIME,
A.NOTE,
A.INC_ACC_ATTRIBUTE,
A.DEFAULT_TRADE_ACC,
A.OP_DEFAULT_TRADE_ACC,
A.ALLOW_MULTI_APPLY,
NULL AS OP_USER,
A.UPDATE_DATE AS OP_DATE,
A.UPDATE_TIME AS OP_TIME,
NULL AS OP_TYPE,
NULL AS OP_STATUS,
NULL AS OP_NOTE,
NULL AS CHECK_USER,
NULL AS CHECK_DATE,
NULL AS CHECK_TIME,
NULL AS CHECK_STATUS,
NULL AS CHECK_NOTE
FROM EA_IUOP.ACC_INV_ACC_INFO A, EA_IUOP.ACC_INV_ACC_COMBINA_FUND_RELA B,EA_IUOP.ACC_INV_ACC_BUSI_DEPT_RELA C
WHERE A.INV_ACC = B.INV_ACC(+)
AND A.TRADE_MKT_CODE = B.TRADE_MKT_CODE(+)
AND A.INV_ACC = C.INV_ACC(+)
AND A.TRADE_MKT_CODE = C.TRADE_MKT_CODE(+)
AND NOT EXISTS (SELECT 1
FROM EA_IUOP.ACC_INV_ACC_APPLY C
WHERE C.INV_ACC = A.INV_ACC
AND C.TRADE_MKT_CODE = A.TRADE_MKT_CODE
AND C.CHECK_STATUS = '0')
GROUP BY A.INV_ACC,
A.INV_NAME,
C.BUSI_DEPT_CODE,
A.REG_ORG,
A.TRADE_MKT_CODE,
A.INV_ACC_TYPE,
A.INV_ACC_STATUS,
A.OPEN_DATE,
A.CLOSE_DATE,
A.TRUST_SEAT,
A.ASSIGN_STATUS,
A.RELA_INV_ACC,
A.SETTLE_ENTITY_ID,
A.UPDATE_DATE,
A.UPDATE_TIME,
A.NOTE,
A.INC_ACC_ATTRIBUTE,
A.DEFAULT_TRADE_ACC,
A.OP_DEFAULT_TRADE_ACC,
A.ALLOW_MULTI_APPLY,
A.UPDATE_DATE,
A.UPDATE_TIME)K
GROUP BY
K.OP_ID,
K.INV_ACC,
K.INV_NAME,
K.REG_ORG,
K.TRADE_MKT_CODE,
K.INV_ACC_TYPE,
K.COMBINA_FUND_CODE,
K.INV_ACC_STATUS,
K.OPEN_DATE,
K.CLOSE_DATE,
K.TRUST_SEAT,
K.ASSIGN_STATUS,
K.RELA_INV_ACC,
K.SETTLE_ENTITY_ID,
K.UPDATE_DATE,
K.UPDATE_TIME,
K.NOTE,
K.INC_ACC_ATTRIBUTE,
K.DEFAULT_TRADE_ACC,
K.OP_DEFAULT_TRADE_ACC,
K.ALLOW_MULTI_APPLY,
K.UPDATE_DATE,
K.UPDATE_TIME