CODEWITHSUNDEEP
mysqlsql
BioGeek
BioGeek

Reputation: 22867

MySQL table with key/value pairs, get keys as column names

I have a MySQL database where I can do the following query:

mysql> SELECT Name, Value from info WHERE ID = 110506;

+-------+-----------+
| Name  | Value     |
+-------+-----------+
| start | 228196    |
| stop  | 228318    |
| fwd   | 0         | 
+-------+-----------+
3 rows in set (0.00 sec)

I am trying to construct a query where the result would be

+--------+--------+-----+
| start  | stop   | fwd |
+--------+--------+-----+
| 228196 | 228318 | 0   |
+------- +--------+-----+
1 row in set (0.00 sec)

I do not know in advance what the names in my name column will be, so I need to somehow dynamically set them from the result of my SELECTquery. How do I do that?

Upvotes: 4

Views: 5368

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25351

Try this

SELECT
    MAX(CASE WHEN name = 'start' THEN value END) AS `start`,
    MAX(CASE WHEN name = 'stop' THEN value END) AS `stop`,
    MAX(CASE WHEN name = 'fwd' THEN value END) AS `fwd`
FROM info
WHERE id = 110506;

Upvotes: 3

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You can use CASE ... WHEN clause on the column values and use the same value as column name for the resulting set. And you have to apply an aggregate function to summarize the results into a single row.

Following example uses MAX aggregate function to summarise the results. 

select
    max( case `name` when 'start' then `value` end ) as `start`
  , max( case `name` when 'stop' then `value` end ) as `stop`
  , max( case `name` when 'fwd' then `value` end ) as `fwd`
from `table_name`
;

Upvotes: 3

Related Questions