From: Peter Brawley
Date: April 10 2006 6:44pm
Subject: Re: Making result rows to one row
List-Archive: http://lists.mysql.com/mysql/196721
Message-Id: <443AA778.7070307@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-443AA7784BC3======="
--=======AVGMAIL-443AA7784BC3=======
Content-Type: multipart/alternative; boundary=------------000400010005070804020309
--------------000400010005070804020309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
--------------000400010005070804020309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------000400010005070804020309--
--=======AVGMAIL-443AA7784BC3=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-443AA7784BC3=======--