SQL Server 2019

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.