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

数据源

SELECT A.C_QBDM,A.C_QBLBMC,B.C_PXXMDM,  B.N_SORT
  FROM T_QB_LBXX A, T_QB_LBXX_PXXM B
 WHERE A.C_QBDM = B.C_QBDM
   AND A.C_PCJGDM = '001' 
   AND A.C_QBDM IN('001001','001002')
ORDER BY A.C_QBDM,B.C_PXXMDM;

将各个C_QBDM的C_PXXMDM、N_SORT列的值串起来

SELECT A.C_QBDM,A.C_QBLBMC,
       LISTAGG(TO_CHAR(B.C_PXXMDM),',') WITHIN GROUP( ORDER BY B.C_PXXMDM) AS C_PXXMDMS,
       LISTAGG(TO_CHAR(B.N_SORT),',') WITHIN GROUP( ORDER BY B.N_SORT) AS N_SORTS
  FROM T_QB_LBXX A, T_QB_LBXX_PXXM B
 WHERE A.C_QBDM = B.C_QBDM
   AND A.C_PCJGDM = '001' --同一培训机构
   AND A.C_QBDM IN('001001','001002')
 GROUP BY A.C_QBDM,A.C_QBLBMC;

/*
  C_QBDM	C_QBLBMC	C_PXXMDMS	        N_SORTS
1	001001	客货	    01001,02001,90016	1,2,4
2	001002	巡游	    09001,90015,90016	1,2,3
*/

 

参考：https://blog.csdn.net/shaotaiban1097/article/details/88327214

可以参考：https://blog.csdn.net/sinat_36257389/article/details/81004843

mysql相同效果的实现  https://blog.csdn.net/sinat_36257389/article/details/95052001

PostgreSQL 相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95611686