From: Peter Brawley Date: July 26 2006 1:25pm Subject: Re: Returning results as a field name List-Archive: http://lists.mysql.com/mysql/200310 Message-Id: <44C76D47.9050007@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44C76D48769E=======" --=======AVGMAIL-44C76D48769E======= Content-Type: multipart/alternative; boundary=------------020908010102070102020007 --------------020908010102070102020007 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > --------------020908010102070102020007 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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
--------------020908010102070102020007-- --=======AVGMAIL-44C76D48769E======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-44C76D48769E=======--