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;
Here is a demo in SQL Server, though the above query should run on most other database as well.