SELECT APC.APC_NAME,

APC.APC_ID,

PTC.PTI_NAME,

TEMP.PTI_ID,

TEMP.COUNTS,

ROW_NUMBER() OVER (PARTITION BY APC.APC_ID ORDER BY TEMP.PTI_ID) ROW_NUMBER

FROM (SELECT APC_ID,

NVL(INC_PTI_ID,8) PTI_ID,

COUNT(*) COUNTS

FROM (SELECT CCI.*

FROM CX_INTEGRITY_CONTENT_INFO CCI

WHERE PTC_ID = '1'

AND INC_RTC_ID = '02')

GROUP BY APC_ID,

INC_PTI_ID) TEMP

LEFT JOIN PROFESSION_TYPE_CODE PTC ON TEMP.PTI_ID = PTC.PTI_ID

RIGHT JOIN ADMIN_PUNISH_CODE APC ON TEMP.APC_ID = APC.APC_ID;

SELECT APC_ID,

APC_NAME,

WM_CONCAT(PTI_ID) PTI_ID,

WM_CONCAT(COUNTS) COUNTS

FROM (SELECT *

FROM (SELECT APC.APC_NAME,

APC.APC_ID,

PTC.PTI_NAME,

NVL(PTC.PTI_ID,8) PTI_ID,

NVL(TEMP.COUNTS,0) COUNTS

FROM (SELECT APC_ID,

INC_PTI_ID,

COUNT(*) COUNTS

FROM (SELECT CCI.*

FROM CX_INTEGRITY_CONTENT_INFO CCI

WHERE PTC_ID = '1'

AND INC_RTC_ID = '02')

GROUP BY APC_ID,

INC_PTI_ID) TEMP

LEFT JOIN PROFESSION_TYPE_CODE PTC ON TEMP.INC_PTI_ID = PTC.PTI_ID

RIGHT JOIN ADMIN_PUNISH_CODE APC ON TEMP.APC_ID = APC.APC_ID))

GROUP BY APC_ID,

APC_NAME;