|
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 AS name,
MAX) AS favorite_color,
MAX) AS likes_dogs
FROM people, answers
WHERE people.id = answers.person_id
GROUP BY people.id
Will yield the following:
|. people.id|. name|. favoritecolor|. likesdogs|
|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
|
| 