SQL subqueries to get field's value as a field name on the result

Question

I have this table, what i want to achive is to get some rows of the detail_key column as column name and the detail_value as the actual row.

Table places

detail_key | detail_value
--------------------------
location   | Athens
country    | Greece
longtitude | 12,3333
weather    | good

I have tried the query below below but only works for 1 field each time.

SELECT detail_value AS location FROM places  WHERE detail_key= 'location';

The result i want to achieve is this below:

location   | country | longtitude
-----------|---------|-------------
 Athens    |Greece   |12,3333

What is your common field? Without something to pivot on there is not a way to do what you are asking... — Nix, May 27 '15 at 23:30
There is not a relation with another table, i just want to take some rows from the `detail_key` column as field names and their `detail_values` as the row. The query in my post works fine, in other words i need something like another 2 of almoast the same query all combined — Makis, May 27 '15 at 23:34
@Makis is there any reason for you to do this directly on MySQL? Or can you use a front end (e.g. PHP, Python, Java, etc)? "Transposing data" can be quite a pain with pure SQL — Barranka, May 27 '15 at 23:36
You just want to pivot, take a look at this : http://stackoverflow.com/questions/7674786/mysql-pivot-table — Hector Sanchez, May 27 '15 at 23:38
@Barranka Thanks for all your answers. I was asked to do it by sql, so i have to find the related query.. — Makis, May 27 '15 at 23:46

score 2 · Accepted Answer · edited May 23 '17 at 11:43

This sounds like a cross table.

MySQL does not include a built-in function for cross tables, but you can build your cross table query "by hand".

Important: You must have a key to group the data. I'll assume that you have a place_id column:

select max(case detail_key when 'location' then detail_value end) as location
     , max(case detail_key when 'country' then detail_value end) as country
     -- and so on
from places
-- add any WHERE conditions here
group by place_id

Hope this helps.

Edit

Your comment made me rethink your problem, and I found a solution here. Here is what you need to do:

Create a variable that holds the expressions you want to apply to get what you need
Create a valid SQL query
Use a prepared statement when your query is ready.

I created a little SQL fiddle for you to see how to solve this, and here it is:

SQL Fiddle

MySQL 5.6 Schema Setup:

create table places(
  id int unsigned not null auto_increment primary key,
  place_id int,
  detail_key varchar(50),
  detail_value varchar(50)
);

insert into places (place_id, detail_key, detail_value) values
(1, 'location','Athens'),(1,'country','Greece'),(1,'longitude','12.3333'),(1,'weather','good');

Query 1:

set @sql = null

Results: (No results)

Query 2:

select group_concat(distinct
                    concat(
                      "max(case detail_key when '",
                      detail_key,
                      "' then detail_value end) as `",
                      detail_key,
                      "`"
                    )
       )
into @sql
from places

Results: (No results)

Query 3:

set @sql = concat("select place_id, ", @sql, " from places group by place_id")

Results: (No results)

Query 4:

prepare stmt from @sql

Results: (No results)

Query 5:

execute stmt

Results:

| place_id | location | country | longitude | weather |
|----------|----------|---------|-----------|---------|
|        1 |   Athens |  Greece |   12.3333 |    good |

Final edit

If you somehow created the above table with the data corresponding to just one place (i.e. there's no place_id and all details are from a single place), you can do something like this:

select max(case detail_key when 'location' then detail_value end) as location
     , max(case detail_key when 'country' then detail_value end) as country
     -- and so on
from places
-- add any WHERE conditions here
group by null;

@Makis Great! I edited my answer to include a working example, including a way for you to avoid creating the expressions "by hand" and let MySQL create the full SQL query — Barranka, May 27 '15 at 23:58

SQL subqueries to get field's value as a field name on the result

1 Answers1

Edit

Final edit