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

Solution 1:

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;

screen capture from demo link below

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