List:General Discussion« Previous MessageNext Message »
From:Mark Dale Date:July 26 2006 8:29am
Subject:RE: Returning results as a field name
View as plain text  
 
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



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