with data as (
SELECT 1 CID, 1 PID , TO_DATE('01/01/2022', 'MM/DD/YYYY') DT, 120 CODE FROM DUAL UNION ALL
SELECT 1 CID, 1 PID , TO_DATE('01/01/2022', 'MM/DD/YYYY') DT, 110 CODE FROM DUAL UNION ALL
SELECT 1 CID, 1 PID , TO_DATE('01/01/2022', 'MM/DD/YYYY') DT, 111 CODE FROM DUAL UNION ALL
SELECT 1 CID, 1 PID , TO_DATE('01/01/2022', 'MM/DD/YYYY') DT, 111 CODE FROM DUAL UNION ALL
SELECT 2 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 222 CODE FROM DUAL UNION ALL
SELECT 2 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 333 CODE FROM DUAL UNION ALL
SELECT 2 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 444 CODE FROM DUAL UNION ALL
SELECT 3 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 110 CODE FROM DUAL UNION ALL
SELECT 3 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 666 CODE FROM DUAL UNION ALL
SELECT 3 CID, 1 PID , TO_DATE('01/02/2022', 'MM/DD/YYYY') DT, 666 CODE FROM DUAL UNION ALL
SELECT 4 CID, 1 PID , TO_DATE('01/03/2022', 'MM/DD/YYYY') DT, 120 CODE FROM DUAL UNION ALL
SELECT 4 CID, 1 PID , TO_DATE('01/03/2022', 'MM/DD/YYYY') DT, 110 CODE FROM DUAL
)
i want the following output:
CID PID DT CODE COUNT
1 1 01/01/2022 120,110 1
1 1 01/01/2022 111 2
2 1 01/02/2022 222 1
2 1 01/02/2022 333 1
2 1 01/02/2022 444 1
3 1 01/02/2022 110 1
3 1 01/02/2022 666 2
4 1 01/03/2022 120, 110 1
Basically, i wan to do a count by cid, pid, dt and code. if the same cid, pid has a code of 120 and 110 in the same date, i want to count it as one row and append both code together. for example, for cid=1 and pid=1, for the same dt of 1/1/2022 , you will notice a row with 110 and a row with 120. since these two codes are on the same date then it should be counted as 1 and both code concatinate.
however, if a specific date has either 110 or 120 in the same date then count is 1 and display either code. if there are any other code other than 120,111, then it should be counted as normal count.
can someone help me write a query to produce the above output? i was thinking maybe row_number analytical function may work but i dont know who to check if 120 and 110 code are in the same date for a specific cid/pid combination.
thanks in advance