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