|
|
Posted by 4partee on 01/19/06 04:56
On Wed, 18 Jan 2006 16:32:51 -0600, Thomas Bartkus wrote:
> "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.
Agree. MS Access is a good tool for ad-hoc reporting. I moved to LAMP
about 3 years ago. Not going back.
>
> Wish we had that in MySQL :-(
> Thomas Bartkus
The SQL could be generated, and executed' in line in PHP by looping
through a 'select distinct class'.....
This includes the rest of the solution that I posted in a followup post.
John
Navigation:
[Reply to this message]
|