How to get fields in Mysql from a couple of name/value fields

Question

I have this data stored in the table in Mysql 5 database.

Table data

| attrName   | attrValue                |
-----------------------------------------
| price       | 12.23                   |
| current_p   | 23.99                   |
| price_dev   | 0.1222                  |
| timestamp   | 2009-07-14 00:00:00 EDT |
| price       | 14.23                   |
| current_p   | 13.11                   |
| price_dev   | 0.7444                  |
| timestamp   | 2009-07-14 00:01:00 EDT |
| price       | 11.00                   |
| current_p   | 18.55                   |
| price_dev   | 0.3572                  |
| timestamp   | 2009-07-14 00:02:00 EDT |

Now I want to do a query to display all data in this way (order by timestamp)

| timestamp               | price   | current_p | price_dev |
-------------------------------------------------------------
| 2009-07-14 00:00:00 EDT | 12.23   | 23.99       | 0.1222  |
| 2009-07-14 00:01:00 EDT | 14.23   | 13.11       | 0.7444  |
| 2009-07-14 00:02:00 EDT | 11.00   | 18.55       | 0.3572  |

Could you suggest me the right query?

Thanks a lot,

pasquy73

Answer 1

You seem to be relying on the order of rows in the table to determine the "groups" of attributes.

But in SQL, there is no implicit order or position for rows in a table, you can only order or group rows by some value column.

So you must add a column to associate them:

| eventId | attrName   | attrValue                |
-------------------------------------------------
|    1234 | price       | 12.23                   |
|    1234 | current_p   | 23.99                   |
|    1234 | price_dev   | 0.1222                  |
|    1234 | timestamp   | 2009-07-14 00:00:00 EDT |
|    1235 | price       | 14.23                   |
|    1235 | current_p   | 13.11                   |
|    1235 | price_dev   | 0.7444                  |
|    1235 | timestamp   | 2009-07-14 00:01:00 EDT |
|    1236 | price       | 11.00                   |
|    1236 | current_p   | 18.55                   |
|    1236 | price_dev   | 0.3572                  |
|    1236 | timestamp   | 2009-07-14 00:02:00 EDT |

Then you can use various solutions, which are well-documented in other places under the entity-attribute-value tag.

For example:

SELECT eventId,
  MAX(CASE attrName WHEN 'timestamp' THEN attrValue END) as timestamp,
  MAX(CASE attrName WHEN 'price' THEN attrValue END) as price,
  MAX(CASE attrName WHEN 'current_p' THEN attrValue END) as current_p,
  MAX(CASE attrName WHEN 'price_dev' THEN attrValue END) as price_dev,
FROM WhateverMyTableNameIs
GROUP BY eventid

If you don't want to do that, then you must store your data differently, putting attributes in proper columns instead of this key-value design.