CODEWITHSUNDEEP
mysql
Peet
Peet

Reputation: 11

Mysql query rows to columns but columns separed by meta_key

i've got an itemmeta table like this: 

item_id meta_key     meta_value
      1 variation_id        123
      1 qty                   3
      2 variation_id        100
      2 qty                   2
      3 variation_id        123
      3 qty                   4

What i need:

123 7
100 2

I can do what i want with a subquery like: 

SELECT DISTINCT m1.meta_value AS variation_id,
     ( SELECT SUM(m2.meta_value) 
       FROM itemmeta m2 
       WHERE m2.meta_key='qty' AND m1.item_id = m2.item_id ) AS qty 
FROM itemmeta m1
WHERE m1.meta_key = 'variation_id'

But i wondering are there any better, simpler or more efficient solution?

Thank you!

Upvotes: 1

Views: 69

Answers (2)

Strawberry
Strawberry

Reputation: 33945

E.g. - though this isn't the fastest method

SELECT variation_id
     , SUM(qty) qty
  FROM 
     ( SELECT MAX(CASE WHEN meta_key = 'variation_id' THEN meta_value END) variation_id
            , MAX(CASE WHEN meta_key = 'qty' THEN meta_value END) qty 
         FROM my_table 
        GROUP 
           BY item_id
     ) x
 GROUP
    BY variation_id;

Upvotes: 0

Chris Lear
Chris Lear

Reputation: 6742

Here's how to do it with a join

 select m.meta_value, sum(m2.meta_value) 
 from itemmeta m 
 join itemmeta m2
 on m.item_id = m2.item_id and m.meta_key = 'variation_id' and m2.meta_key = 'qty' 
 group by m.meta_value;

Upvotes: 1

Related Questions