List: General Discussion « Previous MessageNext Message » From: Peter Lauri Date: April 11 2006 2:03am Subject: RE: Making result rows to one row View as plain text
```-----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

```