Yes, you are correct. I tried it but I got some errors. I simplified my query and tried it, and it worked. Then I added the more complicated parts after that, and it works VERY good.
This is what makes life worth living :) -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 9:55 PM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: Making result rows to one row Peter, > Peter Brawley said: > > SELECT > ..., > GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS ' 1 2 3 4 5 6 7 > 8 9' > FROM tour_player_score tps > INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id > WHERE tour_player_id=175 > GROUP BY tsh.id > > This worked, however, it gives me all results in one cell. I would like to > have them sorted in one cell per strokes. Is that possible? Sure, that's what the pivot table example at http://www.artfulsoftware.com/queries.php#36 does. Your query would be something like... SELECT player_id, MAX( IF(hole=1,strokes,'') ) AS 'Hole 1' MAX( IF(hole=2,strokes,'') ) AS 'Hole 22, ... FROM tbl GROUP BY player_id ... PB > The reason for > this is that I have to use each hole as ORDER criteria. If the SUM is equal, > then I have to check the sum of the last nine holes. If they are the same, > then I have to check the result of hole 18, 17, 16 etc. > > This is my exact query as is now: > > SELECT tps.tour_player_id, GROUP_CONCAT(LPAD(strokes,2,' ') SEPARATOR '-') > AS hole_scores, sum(tps.strokes) AS 'score' FROM tour_player_score tps INNER > JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY > tps.tour_player_id ORDER BY score; > > Best regards, > Peter Lauri > > > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
