Aggregate view one to many
see my chatgpt conversionation: https://chatgpt.com/c/37ef7af1-07ed-4e6b-860f-b234fb106eeb
CREATE OR REPLACE v_core_plan_summary AS
SELECT cs_job AS ps_job, PS_ACTIVE_OPS, PS_ACTIVE_ANALYSTS, PS_SUM_HRS
FROM core_summary
LEFT JOIN (
SELECT ps_job,
LISTAGG(DISTINCT o.O_NAME, ', ') WITHIN GROUP (ORDER BY ps.ps_job) AS PS_ACTIVE_OPS,
LISTAGG(DISTINCT p_active.P_FULLNAME, ', ') WITHIN GROUP (ORDER BY p_active.P_FULLNAME) AS PS_ACTIVE_ANALYSTS,
SUM(cp.CP_HRS) AS PS_SUM_HRS
FROM CORE_PLAN_PARENT ps
LEFT JOIN CX_OP o ON (o.O_ID = ps.PS_ID_OP) AND (ps.PS_dt_start IS NOT NULL) AND (ps.PS_dt_complete IS NULL)
LEFT JOIN CORE_PLAN cp ON ps.PS_ID = cp.CP_ID_PARENT
LEFT JOIN CX_PERSON p_active ON (p_active.P_ID = cp.CP_ID_PERSON) AND (ps.PS_dt_start IS NOT NULL) AND (ps.PS_dt_complete IS NULL)
GROUP BY ps_job
) ps_summary ON ps_job = cs_job;
For MySQL or SQLlite use GROUP_CONCAT
For SQL Server 2017 and Postgres, use STRING_AGG