Consider the following tables:
id | name |
---|---|
1 | Jack |
2 | Jane |
3 | Raja |
4 | Aaliyah |
id | text |
---|---|
1 | What’s your favorite color? |
2 | Do you like dogs? |
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