Insert into table with multiple select statements in MY SQL

Question

I have the result from a query like:

+------------------+------------+
| meta_key         | meta_value |
+------------------+------------+
| Destination Name | Shivapuri  |
| Destination Date | 26/03/2012 |
+------------------+------------+

I am trying to write a select statement with the Column name as Destination Name and Destination Date whose respective values are Shivapuri and '26/03/2012'. How is this possible to do with a query in MY SQL?

This is called a pivot table and has been answered many times before on SO: http://stackoverflow.com/questions/7674786/mysql-pivot-table — liquorvicar, Apr 06 '12 at 11:12
Pivot is rather unneeded, Since by the nature of the structure, there is only going to be one `destination name` and `destination date`. — Starx, Apr 06 '12 at 11:27

score 1 · Answer 1 · answered Apr 06 '12 at 11:44

Something along these lines should do it -

SELECT
    GROUP_CONCAT(IF(meta_key = 'Destination Name', meta_value, NULL)) AS `Destination Name`,
    GROUP_CONCAT(IF(meta_key = 'Destination Date', meta_value, NULL)) AS `Destination Date`
FROM tbl_name
GROUP BY record_identifier

score 1 · Answer 2 · answered Apr 06 '12 at 11:48

1

SELECT 
    (CASE WHEN meta_key = 'Destination Name' THEN meta_value END) as name,
    (CASE WHEN meta_key = 'Destination Date' THEN meta_value END) as date
FROM `yourtable`

answered Apr 06 '12 at 11:48

Starx

77,474
47
185
261

Insert into table with multiple select statements in MY SQL

2 Answers2