List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 26 2006 1:18pm
Subject:Re: Returning results as a field name
View as plain text  
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
>
>
>
>
>
>   

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
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