0

This is the structure of my table: enter image description here

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

enter image description here

How can I convert "index_name" rows to columns like:

date | MSCI EAFE Mid NR USD | Alerian MLP PR USD etc

enter image description here

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!

peterm
  • 91,357
  • 15
  • 148
  • 157
user2723490
  • 2,010
  • 4
  • 27
  • 37
  • I'm pretty sure I've seen this question asked at least once before. Maybe this: http://stackoverflow.com/questions/4078983/is-there-a-way-to-pivot-rows-to-columns-in-mysql-without-using-case?rq=1 – Buttle Butkus Nov 05 '13 at 02:49
  • That solution requires multiply joins, which makes the query complicated for a large number of fields. The best I have found so far is this: SELECT `date`, MAX(IF(`index_name` = 'Alerian MLP PR USD' AND `time_period`='M1', results, NULL)) AS `Alerian MLP PR USD`, MAX(IF(`index_name` = 'MSCI EAFE Mid NR USD' AND `time_period`='M1', results, NULL)) AS `MSCI EAFE Mid NR USD` FROM `mst_ind` GROUP BY `date` – user2723490 Nov 05 '13 at 03:28

1 Answers1

0

Try

SELECT date,
       MAX(CASE WHEN index_name = 'Alerian MLP PR USD' THEN results END) `Alerian MLP PR USD`,
       MAX(CASE WHEN index_name = 'MSCI EAFE Mid NR USD' THEN results END) `MSCI EAFE Mid NR USD`
  FROM mst_ind
 WHERE index_name IN ('MSCI EAFE Mid NR USD', 'Alerian MLP PR USD') 
   AND time_period = 'M1'
 GROUP BY date

Sample output:

|       DATE | ALERIAN MLP PR USD | MSCI EAFE MID NR USD |
|------------|--------------------|----------------------|
| 1995-01-31 |            -0.0167 |               0.0335 |
| 1995-02-28 |             0.0128 |               0.0409 |
| 1995-03-31 |             0.0464 |               0.0286 |
| 1995-04-28 |             0.0331 |               0.0297 |
| 1995-05-31 |             0.0069 |               0.0398 |
...

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • It is a very similar solution to what I've mentioned in my post, i.e. 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 – user2723490 Nov 05 '13 at 23:36
  • @user2723490 Yes it's very close. But first of all in your question you specifically stated *...I've tried this code, but it generates an error...*. Apart from that the important difference is that your query fetch all data even though you need only time_period `M1` and then process it while proposed query filters out all but 'M1' on much earlier stages of processing bib applying a condition in `WHERE` clause – peterm Nov 06 '13 at 00:00