Sep 18, 2010
ugliness
I was browsing a "SQL cookbook" to get some inspiration for SQL exercises when I came across a chapter on p*vot tables. The terror...
Apparently, you may want to get from this:
| job | name |
| java developer | nick |
| dba | michael |
| java developer | jenny |
| analyst | beatrice |
| analyst | george |
to this:
| java developer | dba | analyst |
| nick | michael | beatrice |
| jenny | george |
Well, don't! The first table represents a relation, the second doesn't. A relation is a set of tuples -- what sort of tuple does {nick, michael, beatrice} represent?
At best, the second table is some sort of report. SQL is not a language for producing reports. Create your report in something else: a script, a spreadsheet, a word processor. Use simple SELECT ... WHERE job = ... to fetch the appropriate data for each case.
