List:General Discussion« Previous MessageNext Message »
From:Mark Dale Date:July 26 2006 11:34pm
Subject:RE: Returning results as a field name
View as plain text  
Many thanks Peter, that works a treat.
 
Just recapping for others who may be interested.
 
The problem was to get a result table that was displaying like this:

mysql> select * from test;
+-------+-------------+--------+
| name  | question_id | answer |
+-------+-------------+--------+
| Mark  | 100         | Yes    |
| Mark  | 101         | No     |
| Mark  | 102         | Yes    |
| Mark  | 103         | No     |
| Mark  | 104         | Yes    |
| Leigh | 101         | No     |
| Leigh | 102         | Yes    |
| Leigh | 103         | No     |
| Leigh | 104         | Yes    |
| Leigh | 100         | Yes    |
+-------+-------------+--------+
10 rows in set (0.00 sec)
 
to display like this:
 
+-------+------+------+------+------+------+
| name  | Q100 | Q101 | Q102 | Q103 | Q104 |
+-------+------+------+------+------+------+
| Leigh | Yes  | No   | Yes  | No   | Yes  |
| Mark  | Yes  | No   | Yes  | No   | Yes  |
+-------+------+------+------+------+------+
2 rows in set (0.01 sec)

Solution:

mysql> SELECT
    ->   name,
    ->   MAX( IF(question_id=100,answer,'') ) AS Q100,
    ->   MAX( IF(question_id=101,answer,'') ) AS Q101,
    ->   MAX( IF(question_id=102,answer,'') ) AS Q102,
    ->   MAX( IF(question_id=103,answer,'') ) AS Q103,
    ->   MAX( IF(question_id=104,answer,'') ) AS Q104
    -> FROM test
    -> GROUP BY name;
 
 




 

________________________________

From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Wednesday, 26 July 2006 11:25 PM
To: peter.brawley@stripped
Cc: Mark Dale; mysql@stripped
Subject: Re: Returning results as a field name


Pardon me, too early & not enough coffee, that's not quite the
'max-concat trick', but it oughtta work.

PB

-----

Thread
Returning results as a field nameMark Dale26 Jul
  • Re: Returning results as a field nameChris26 Jul
RE: Returning results as a field nameMark Dale26 Jul
  • Re: Returning results as a field namePeter Brawley26 Jul
    • Re: Returning results as a field namePeter Brawley26 Jul
      • RE: Returning results as a field nameMark Dale27 Jul