RE: Returning results as a field name
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:[EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning results as a field name
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:[EMAIL PROTECTED]] 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 outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning results as a field name
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:[EMAIL PROTECTED]] 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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Returning results as a field name
Many thanks Peter, that works a treat. Just recapping for others who may be interested. The problem was to get a result table that was displaying like this: 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) to display like this: +---+--+--+--+--+--+ | name | Q100 | Q101 | Q102 | Q103 | Q104 | +---+--+--+--+--+--+ | Leigh | Yes | No | Yes | No | Yes | | Mark | Yes | No | Yes | No | Yes | +---+--+--+--+--+--+ 2 rows in set (0.01 sec) Solution: mysql SELECT - name, - MAX( IF(question_id=100,answer,'') ) AS Q100, - MAX( IF(question_id=101,answer,'') ) AS Q101, - MAX( IF(question_id=102,answer,'') ) AS Q102, - MAX( IF(question_id=103,answer,'') ) AS Q103, - MAX( IF(question_id=104,answer,'') ) AS Q104 - FROM test - GROUP BY name; From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 11:25 PM To: [EMAIL PROTECTED] Cc: Mark Dale; mysql@lists.mysql.com Subject: Re: Returning results as a field name Pardon me, too early not enough coffee, that's not quite the 'max-concat trick', but it oughtta work. PB - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning results as a field name
Hello MySQL List 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| ++---+---+ cheers Mark Dale [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning results as a field name
Mark Dale wrote: Hello MySQL List 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| ++---+---+ No, but you can get it to look like this: mysql select n.name, n.question_id, n2.answer from blah n, blah n2 where n.name=n2.name and n.answer=n2.answer; +---+-++ | name | question_id | answer | +---+-++ | chris | 1 | yes| | chris | 2 | no | +---+-++ 2 rows in set (0.00 sec) You need to do a self-join on all but one column so you can get the rows to become columns. Make sure you have indexes on all joining columns otherwise it will be slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
