In case it is acceptable to have a1,b1.qty, b2.qty, b3.qty concatenated in one field GROUP_CONCAT might help
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_ group-concat -- Jacques Brignon > -----Message d'origine----- > De : Baron Schwartz [mailto:[EMAIL PROTECTED] > Envoyé : vendredi 20 avril 2007 14:52 > À : [EMAIL PROTECTED] > Cc : mysql@lists.mysql.com > Objet : Re: How could write this SQL > > Hi, > > wang shuming wrote: > > Hi, > > How could > > select > > row1 a1,b1.qty, b2.qty, b3.qty > > row2 a2,b1.qty, b2.qty, b3.qty > > row3 a3,b1.qty, b2.qty, b3.qty > > ... > > from a table : > > > > row1 a1,b1,qty > > row2 a1,b2,qty > > row3 a1,b3,qty > > row4 a2,b1,qty > > row5 a2,b2,qty > > row6 a2,b3,qty > > row7 a3,b1,qty > > I think you are describing a cross-tabulation (aka pivot table). You can > do this with > joins, though this particular case looks tricky. There is a good article > on > cross-tabulations here: > > http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html > > Baron > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]