MySQL pivot tables - covert rows to columns
- by user2723490
This is the structure of my table:
Then I run a query
SELECT `date`,`index_name`,`results` FROM `mst_ind` WHERE `index_name` IN ('MSCI EAFE Mid NR USD', 'Alerian MLP PR USD') AND `time_period`='M1'
and get a table like
How can I convert "index_name" rows to columns like:
date | MSCI EAFE Mid NR USD | Alerian MLP PR USD etc
In other words I need each column to represent an index and rows to represent date-result. I understand that MySQL doesn't have pivot table functions. What is the easiest way of doing this?
I've tried this code, but it generates an error:
SELECT
`date`,
MAX(IF(index_name = 'Alerian MLP PR USD' AND `time_period`='M1', results, NULL)) AS res1,
MAX(IF(index_name = 'MSCI EAFE Mid NR USD' AND `time_period`='M1', results, NULL)) AS res2
FROM
`mst_ind`
GROUP BY `date
I need to make the conversion on the query level - not PHP. Please suggest a nice and elegant solution. Thanks!
