Consider the following tables:
people
| id |
name |
| 1 |
Jack |
| 2 |
Jane |
| 3 |
Raja |
| 4 |
Aaliyah |
questions
| id |
text |
| 1 |
What’s your favorite color? |
| 2 |
Do you like dogs? |
answers
| person_id |
question_id |
value |
| 1 |
1 |
Red |
| 1 |
2 |
Yes |
| 2 |
1 |
Blue |
| 2 |
2 |
No |
| .. |
.. |
.. |
This
SQL:
SELECT people.id, MAX(people.name) AS name,
MAX(DECODE(answers.question_id, 1, answers.value, NULL)) AS favorite_color,
MAX(DECODE(answers.question_id, 2, answers.value, NULL)) AS likes_dogs
FROM people, answers
WHERE people.id = answers.person_id
GROUP BY people.id
Will yield the following:
| people.id |
name |
favorite_color |
likes_dogs |
| 1 |
Jack |
Red |
Yes |
| 2 |
Jane |
Blue |
No |
| 3 |
Raja |
Green |
No |
| .. |
.. |
.. |
.. |
A coworker of mine, Ken Gibbs, showed me this technique several years ago and I’ve used it frequently since. While this use of MAX and DECODE is hardly unknown , I have recently shown this to a few people who were really excited by it. So I thought I would share it with you, dear reader. Also, note that Oracle’s DECODE can easily be replaced with a CASE in other SQL dialects.
Update: fixed an error in the example SQL