-2

is there any way to have the schema you see here in the SqlFiddle shown as:

SKU | size S | size M | size L | init_qty | qty

? I was trying to use GROUP_CONCAT by it is no what I am looking for.

Here it is my query

select sku, name, group_concat(taglie separator ';') as `Magazzino`
from
(
  select p.sku, p.name, concat(po.valuename, ':',
  group_concat(po.value separator ',')) as taglie
  from products p
  right join products_opt po
  on p.id = po.product
  group by p.sku
) tbl
group by sku;

Any suggestion? Thank you

Community
  • 1
  • 1
Aptivus
  • 433
  • 1
  • 8
  • 24
  • You want to do a crosstab query on an Entity Attribute Value dataset? That's going to result in some ugly SQL. But I have no idea what value you are expecting to see in the sizeX columns when the only value metrics (qty, init_qty) are expressed as different columns in the output. – symcbean Apr 05 '16 at 12:30
  • what I am expecting is something like: `SKU | size S | size M | size L |` `demoprod | 2 | 1 | 1 |` – Aptivus Apr 05 '16 at 12:37
  • Thats completely different from your what you put in your question above. – symcbean Apr 05 '16 at 14:19

1 Answers1

-1

You need to add AUTO_INCREMENT for both id.

SQLFiddle:

http://sqlfiddle.com/#!9/0bde6/2

Needed SQL:

select 
    p.sku, 
    sum(po.value = 'S') as `size S`,
    sum(po.value = 'M') as `size M`,
    sum(po.value = 'L') as `size L`,
    po.product as demoproduct
from products p
right join products_opt po
on p.id = po.product
group by p.sku;

Output:

+-------+--------+--------+--------+-------------+
| sku   | size S | size M | size L | demoproduct |
+-------+--------+--------+--------+-------------+
| PROD1 |      1 |      1 |      1 |           1 |
| PROD2 |      2 |      1 |      2 |           2 |
+-------+--------+--------+--------+-------------+
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • yes Dylan, you're right about the auto_increment. The result of your fiddle is not what I was expecting – Aptivus Apr 05 '16 at 12:41
  • You never mentioned what you are expecting. I update my post based on my guessing... – Dylan Su Apr 05 '16 at 12:48
  • sorry Dylan, my comment has been published but I hadn't finished writing it! I seem rude in my comment. I apologize. Anyway thank you very much for your new query. Why summing? Thanks for your explanation – Aptivus Apr 05 '16 at 14:11