Convert multy rows to columns

Question

I'm trying to retrive data from MySQL data base. The structure of the table (Tab_1) there is:

id   key      value
 1   address   xyz
 1   post_code 120
 1   country   CA

As you can see this is actually order details information.

I want to convert the key column to be an actual column and the value to be the value of this column. meaning:

    id  address  post_code  country
 ------------------------------------
    1    xyz       120         CA

Where id=1 is the key of this order.

The table in MySQL can not be changed. Its part of a close system that we use (WordPress plugin) I just want to write a query that gets some data from it...

My goal is to use it in a join where it would be easier to get the data:

Select x.address ,x.post_code, x.country
from orders
join (...) as x using (order_id=id)
where order_id=1

It should give:

    address  post_code  country
 ------------------------------------
      xyz        120         CA

As you can see it suppose to get all fields of x which is the address,post_code,country etc. the ... in the join is where I need to put the query that convert Tab_1 to a readable stracture for the join.

How can I do that?

Group By will not solve this question. it does not convert the values to be columns.. Note that `key` should become a column while `value` should become the value in this column. When I say in select `x.address` I want it to give me `xyz`. — java, Apr 07 '16 at 08:19

score 2 · Accepted Answer · answered Apr 07 '16 at 08:27

2

Do a GROUP BY, use CASE expressions for the different key types:

select id,
       max(case when key = 'address' then value end) as address,
       max(case when key = 'post_code' then value end) as post_code,
       max(case when key = 'country' then value end) as country
from orders
group by id

answered Apr 07 '16 at 08:27

jarlh

42,561
8
45
63

To pick the non-null value. You can use MIN also. (I assume there's only one address/post_code/country per id.) – jarlh Apr 07 '16 at 08:40
yes there is only one... this is why i don't understand why max nor min is needed... – java Apr 07 '16 at 08:43
If an id has 3 rows, the case expressions returns "null, null, value". Use max or min to pick the non-null value. – jarlh Apr 07 '16 at 08:45

score 0 · Answer 2 · answered Apr 07 '16 at 08:42

0

You can use only 4 selects:

SELECT id, (SELECT value FROM Tab_1 WHERE id = 1 AND `key` = 'address') AS address,
(SELECT value FROM Tab_1 WHERE id = 1 AND `key` = 'post_code') AS post_code,
(SELECT value FROM Tab_1 WHERE id = 1 AND `key` = 'country') AS country FROM Tab_1
GROUP BY id;

answered Apr 07 '16 at 08:42

Kaspy

54
1
10

1

If you're doing this, at least have correlated sub-queries. (Now you always return id = 1 values.) – jarlh Apr 07 '16 at 08:46

Convert multy rows to columns

2 Answers2