By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH CAMPAIGN AS (
SELECT 1 AS ID, 'Campaign A' AS campaign_name UNION ALL
SELECT 2, 'Campaign B'
),
PARTICIPANT AS (
SELECT 1 AS ID, 1 AS campaign_id, 'Alice' AS participant_name UNION ALL
SELECT 2, 1, 'Ben'
),
CUSTOM_FIELD AS (
SELECT 1 AS ID, 1 AS campaign_id, 'Gender' AS field_name UNION ALL
SELECT 2, 1, 'Age'
),
FIELD_ANSWER AS (
SELECT 1 AS ID, 1 AS participant_id, 1 AS field_id, 'Female' AS answer UNION ALL
SELECT 2, 1, 2, '24' UNION ALL
SELECT 3, 2, 1, 'Male' UNION ALL
SELECT 4, 2, 2, '28'
)
SELECT
c.campaign_name,
p.participant_name,
MAX(CASE WHEN cf.field_name = 'Gender' THEN fa.answer END) AS Gender,
MAX(CASE WHEN cf.field_name = 'Age' THEN fa.answer END) AS Age
FROM CAMPAIGN c
INNER JOIN PARTICIPANT p
ON p.campaign_id = c.ID
INNER JOIN FIELD_ANSWER fa
ON fa.participant_id = p.ID
INNER JOIN CUSTOM_FIELD cf
ON cf.ID = fa.field_id AND cf.campaign_id = c.ID
GROUP BY
c.campaign_name,
p.participant_name;
| campaign_name | participant_name | Gender | Age |
|---|---|---|---|
| Campaign A | Alice | Female | 24 |
| Campaign A | Ben | Male | 28 |
Warning: Null value is eliminated by an aggregate or other SET operation.