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:peter.brawley@stripped]
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
