Question 20314 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL. Select and split GROUP_CONCAT into different columns

I’m noob in MySQL…
It is difficult to describe my task, so I will show it with an example.

I have two tables:

  1. meta_data:

enter image description here

id  key value
1   property_1  1,2,54,2
1   property_2  23424
1   property_3  cat
1   property_4  4324
1   property_5  3231
1   property_6  45
1   property_7  423424
1   property_8  868
1   property_9  2424
1   property_10 886
1   property_11 535
1   property_12 3434
1   property_13 686
1   property_14 55
1   property_15 23424
1   property_16 54534
1   property_17 868
1   property_18 4556
1   property_19 5345
1   property_20 43535
2   property_1  54,92,22
2   property_2  43443
2   property_3  dog
2   property_4  343
2   property_5  2342
2   property_6  34
  1. users:

enter image description here

id  email
1   [email protected]
2   [email protected]
3   [email protected]
4   [email protected]
5   [email protected]
6   [email protected]
7   [email protected]
8   [email protected]
9   [email protected]

And after call the stored procedure I want to get this:

enter image description here

id  property_1  property_2  property_3  email
1   "1,2,54,2"  "23424"     "cat"       "[email protected]"

The procedure must get an input parameter (for example "cat") and:

  1. find the line where property_3 is equal to the input parameter and get the id from this row (for example "1"),
  2. select the fields, that I need, from the meta_data table, where the id is equal to our id from the first point (for example property_1, property_2, property_3),
  3. then rotate them from rows to columns,
  4. and finally add email from user table with the same id.

All this if a property_3 match was found.

I think I can do it with lots of SELECT/FROM, but it seems too complicated to me.

I tried to use CASE WHEN ___ LIKE ___ THEN ___ END but I couldn’t succeed. In this case I get cells with null after grouping:

SELECT id,
       CASE WHEN meta_data.key LIKE 'property_1' THEN meta_data.value END AS property_1,
       CASE WHEN meta_data.key LIKE 'property_2' THEN meta_data.value END AS property_2,
       CASE WHEN meta_data.key LIKE 'property_3' THEN meta_data.value END AS property_3
FROM meta_data
Tags:

2

Answers


  1. 0

    I would guess:

    SELECT 
   id, 
   meta_data1.value AS property_1,
   meta_data2.value AS property_2,
   meta_data3.value AS property_3,
   email
FROM 
   meta_data AS meta_data1, 
   meta_data AS meta_data2, 
   meta_data AS meta_data3,
   users
WHERE
   meta_data1.id = users.id AND
   meta_data2.id = users.id AND
   meta_data3.id = users.id AND
   meta_data1.key = "property_1" AND
   meta_data2.key = "property_2" AND
   meta_data3.key = "property_3"
    Login or Signup to reply.
  2. 0

    This an optimized way to do it using GROUP BY with the aggregate function MAX():

    SELECT m.id,
       MAX(CASE WHEN m.key_ LIKE 'property_1' THEN m.value_ END) AS property_1,
       MAX(CASE WHEN m.key_ LIKE 'property_2' THEN m.value_ END) AS property_2,
       max(CASE WHEN m.key_ LIKE 'property_3' THEN m.value_ END) AS property_3,
       MAX(u.email)
FROM meta_data m
inner join users u on u.id = m.id
group by m.id

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search