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.
