cornell cornell - 5 months ago 20
MySQL Question

Mysql rows to columns

I have the following mysql table:

+----+-----+-----+--------+
| id | sid | tid | val |
+----+-----+-----+--------+
| 1 | 1 | 1 | square |
| 2 | 1 | 2 | big |
| 3 | 1 | 3 | red |
| 4 | 2 | 1 | circle |
| 5 | 2 | 2 | small |
| 6 | 2 | 3 | yellow |
+----+-----+-----+--------+


And I would need a query to get the following results:

+-----+--------+-------+--------+
| sid | figure | size | colour |
+-----+--------+-------+--------+
| 1 | square | big | red |
| 2 | circle | small | yellow |
+-----+--------+-------+--------+


Any ideas?

Thanks.

Answer

You didn't provide any details about how you determine the new column names but based on your data I am guessing that it is based on the values in the tid column. You can use an aggregate function with a case expression to get the result:

select 
  sid,
  max(case when tid = 1 then val end) figure,
  max(case when tid = 2 then val end) size,
  max(case when tid = 3 then val end) color
from yourtable
group by sid;

See SQL Fiddle with Demo