Thanks for the start Peter. I have got that 'Pivot table using a JOIN' query returning results for 2 records just like the example but I'm stuck in modifying to get the result for the five questions (100 to 104).
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) Adapting the query for my table like so: select t1.name, answer as '100', t2.101 from test as t1 inner join ( select name,answer as '101' from test where question_id='101' ) as t2 on t1.name=t2.name and t1.question_id='100'; +-------+------+------+ | name | 100 | 101 | +-------+------+------+ | Mark | Yes | No | | Leigh | Yes | No | +-------+------+------+ 2 rows in set (0.00 sec) Any clues to get the other questions and their answers into the table greatly appreciated. cheers Mark Dale ******************************************************* -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 2:54 PM To: Mark Dale Subject: Re: Returning results as a field name >I have a simple table that outputs results like so: >select name, question_id, answer from table; > +-------+--------------+---------+ > |NAME |QUESTION_ID |ANSWER | > +-------+--------------+---------+ > |Mark |100 |Yes | > |Mark |101 |No | > |Leigh |100 |Yes | > |Leigh |101 |No | > +-------+--------------+---------+ > Is there a way to query things so the result looks like this: > select ??? > +--------+-----------+-----------+ > |NAME |100 |101 | > +--------+-----------+-----------+ > |Mark |Yes |No | > |Leigh |No |Yes | > +--------+-----------+-----------+ Have a look at 'Pivot table using a join' at http://www.artfulsoftware.com/queries.php PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
