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:[EMAIL PROTECTED]
Sent: Wednesday, 26 July 2006 11:25 PM
To: [EMAIL PROTECTED]
Cc: Mark Dale; mysql@lists.mysql.com
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
-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
