Denormalization table – SQL Select (Pivot Table?)

CAMPAIGN table

ID	campaign_name
1	Campaign A
2	Campaign B

PARTICIPANT table

ID	campaign_id	participant_name
1	1	Alice
2	1	Ben

CUSTOM_FIELD table

ID	campaign_id	field_name
1	1	Gender
2	1	Age

FIELD_ANSWER table

ID	participant_id	field_id	answer
1	1	1	Female
2	1	2	24
3	2	1	Male
4	2	2	28

With these tables in above, can we query a result as shown below?

Campaign Name	Participant Name	Gender	Age
Campaign A	Alice	Female	24
Campaign A	Ben	Male	28

Answer

Using pivoting logic we can try:

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;

Here is a demo in SQL Server, though the above query should run on most other database as well.