MySQL Column Value Pivot
- by manyxcxi
I have a MySQL InnoDB table laid out like so:
id (int), run_id (int), element_name (varchar), value (text), line_order, column_order
`MyDB`.`MyTable` (
`id` bigint(20) NOT NULL,
`run_id` int(11) NOT NULL,
`element_name` varchar(255) NOT NULL,
`value` text,
`line_order` int(11) default NULL,
`column_order` int(11) default NULL
It is used to store data generated by a Java program that used to output this in CSV format, hence the line_order and column_order.
Lets say I have 2 entries (according to the table description):
1,1,'ELEMENT 1','A',0,0
2,1,'ELEMENT 2','B',0,1
I want to pivot this data in a view for reporting so that it would look like more like the CSV would, where the output would look this:
---------------------
|ELEMENT 1|ELEMENT 2|
---------------------
| A | B |
---------------------
The data coming in is extremely dynamic; it can be in any order, can be any of over 900 different elements, and the value could be anything. The Run ID ties them all together, and the line and column order basically let me know where the user wants that data to come back in order.
