Pardon me, too early & not enough coffee, that's not quite the
'max-concat trick', but it oughtta work.
PB
-----
Peter Brawley wrote:
> Mark,
> >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).
> Right. The 'max-concat trick', 'Group column statistics in rows',
> might be easier, eg...
> SELECT
> name,
> MAX( IF(question=100,answer,'') ) AS Ans100,
> MAX( IF(question=101,answer,'') ) AS Ans101,
> MAX( IF(question=102,answer,'') ) AS Ans102,
> MAX( IF(question=103,answer,'') ) AS Ans103,
> MAX( IF(question=104,answer,'') ) AS And104
> FROM tbl
> GROUP BY name
>
> PB
>
> -----
>
> Mark Dale wrote:
>>
>> 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
>>
>>
>>
>>
>>
>>
> ------------------------------------------------------------------------
>
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006
