List:General Discussion« Previous MessageNext Message »
From:Chris Date:July 26 2006 4:35am
Subject:Re: Returning results as a field name
View as plain text  
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.

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