From: Peter Brawley
Date: July 26 2006 1:18pm
Subject: Re: Returning results as a field name
List-Archive: http://lists.mysql.com/mysql/200309
Message-Id: <44C76B97.1090708@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44C76B9864A9======="
--=======AVGMAIL-44C76B9864A9=======
Content-Type: multipart/alternative; boundary=------------020900090702040000070007
--------------020900090702040000070007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
>
>
>
>
>
--------------020900090702040000070007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
--------------020900090702040000070007--
--=======AVGMAIL-44C76B9864A9=======
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-44C76B9864A9=======--