List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 10 2006 6:44pm
Subject:Re: Making result rows to one row
View as plain text  
Peter,
> 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?
>   
It can be done roughly with something like ...

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

or more thoroughly with a crosstab (eg 
http://www.artfulsoftware.com/queries.php#36)

PB

-----

> Best regards,
> Peter Lauri
>
>   
> ------------------------------------------------------------------------
>
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006
>   

Attachment: [text/html]
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
Thread
Making result rows to one rowPeter Lauri10 Apr
RE: Making result rows to one rowRobert DiFalco10 Apr
  • RE: Making result rows to one rowPeter Lauri11 Apr
Re: Making result rows to one rowPeter Brawley10 Apr
  • RE: Making result rows to one rowPeter Lauri11 Apr
Re: Making result rows to one rowPeter Brawley11 Apr
  • RE: [SOLVED] Making result rows to one rowPeter Lauri12 Apr