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

1 answer

  • answered 2022-01-13 05:20 Tim Biegeleisen

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum