Hello All,
I wrote below SQL using LISTAGG function, but it is giving me duplicate value as shown below.. I also mention here expected output.
| SELECT DISTINCT ppg.profile_id AS pid, |
| LISTAGG ( vegb.bu_id, ',') WITHIN GROUP ( |
ORDER BY vegb.bu_name) AS bu_id,
| LISTAGG (vegb.bu_name, ',') WITHIN GROUP ( |
ORDER BY vegb.bu_name) AS bu_name
FROM profile_data pd,
| PROFILE_PLATFORM_GROUPS ppg, |
| V_EQUIPMENT_GROUP_BU vegb |
| WHERE pd.profile_id | = ppg.profile_id |
AND ppg.verifier_group_id = vegb.verifier_group_id
GROUP BY ppg.profile_id;
Current Result as below:
PID BU_ID BU_NAME
------------------------------------------------------------------------------------------------------------------------
1141 1000,1000,1000,1000,1000,1000 Corporate,Corporate,Corporate,Corporate
1151 1000 East London Team
2046 1075 Access,Corporate
2276 1000,1000,1025 Corporate,Corporate,FTTx I&R
Expected Result as below:
PID BU_ID BU_NAME
------------------------------------------------------------------------------------------------------------------------
1141 1000 Corporate
1151 1000 East London Team
2046 1075 Access,Corporate
2276 1000,1000,1025 Corporate,FTTx I&R