Posted by Thomas Bartkus on 01/19/06 00:32

"4partee" <spamu@voyager.starfleet> wrote in message
news:pan.2006.01.17.03.29.00.116088@voyager.starfleet...
> On Sun, 15 Jan 2006 19:28:14 -0500, Jerry Stuckle wrote:
>
> > manny wrote:
> >> I wish I knew what this was called, and I could have made a more
sensible
> >> subject. For that matter, I knew what it was called, I could have
googled it!
> >>
> >> Anyway, let's I have a table with name, class, grade.
> >>
> >> joe bio a
> >> jill chem b
> >> john psych b
> >> john chem a
> >> jill pysch a
> >>
> >> and I want to display the results as
> >>
> >> name bio psych chem
> >> joe a - -
> >> jill - a b
> >> john - b a
> >>
> >> Is there a way to do this with sql? How about sql with php?
> >>
> >> Thanks much!
> >>
> >> Manny
> >
>
> SELECT name, IF(class = 'bio', grade,'-') AS `bio`
> , IF(class = 'psych', grade,'-') AS `psych`
> , IF(class = 'chem', grade,'-') AS `chem`
> from tablex
>
> Actually, mysql really sucks at this!

Actually, it's standard that SQL sucks at this.
You can't do the above unless the list of discreet values in [class] is
short and static.

Giving the devil his due - the Microsoft JET flavor of SQL has GROUP with
PIVOT that makes short work out of problems like this. I *think* Oracle has
similar extensions.

Wish we had that in MySQL :-(
Thomas Bartkus

[Back to original message]