-----Original Message-----
From: Peter Lauri [mailto:peter@stripped]
Sent: Monday, April 10, 2006 10:45 AM
To: mysql@stripped
Subject: Making result rows to one row
Best group member,
Many of you probably do not understand the question; neither would I if
someone ask me, so I will explain a little bit more.
This is what I have:
SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole
tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY
tsh.hole_number;
This generates this:
+---------+
| strokes |
+---------+
| 6 |
| 4 |
| 5 |
| 3 |
| 5 |
| 4 |
| 4 |
| 3 |
| 6 |
+---------+
I would like to retrieve it in one row instead of many rows with one per
row.
s1 s2 s3 s4 s5 s6 s7 s8 s9
6 4 5 3 5 4 4 3 6
Can this be done?
Best regards,
Peter Lauri
-----Original Message-----
From: Robert DiFalco [mailto:rdifalco@stripped]
Sent: Tuesday, April 11, 2006 12:47 AM
To: Peter Lauri; mysql@stripped
Subject: RE: Making result rows to one row
Can you tell us more about the use case? Why does this need to be in a
single row? Maybe instead of figuring out how to get this into a single
row we could instead figure out how to solve the problem that requires
it to be in a single row.
*************************************
My goal is to create a leaderboard for golf in just one query. I want one
row to be for one player. The final result will be something like this:
|
Peter Lauri 4 3 4 6 5 4 2 5 3 36
Tiger Woods 5 5 4 4 4 4 4 2 4 37
|
The information for the rows I get with this queries:
//Hole scores (one resultset with 9 rows)
SELECT tps.strokes FROM tour_player_score tps join tour_scorecard_hole
tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY
tsh.hole_number;
//Sum of all holes (one resultsit with 1 rows)
SELECT sum(tps.strokes) FROM tour_player_score tps join tour_scorecard_hole
tsh WHERE tour_player_id=175 AND tps.scorecard_hole_id=tsh.id ORDER BY
tsh.hole_number;
Best regards,
Peter Lauri
