How to pivot two values in mysql? -
i've following table structure:
+-------+----------+------+------------+------------+ | agent | product | type | value_2013 | value_2014 | +-------+----------+------+------------+------------+ | john | product1 | | 10 | 11 | | john | product1 | c | 14 | 13 | | mike | product1 | | 11 | 20 | | mike | product2 | c | 13 | 15 | +-------+----------+------+------------+------------+
type or c need transform (pivot) in table
agent, product, type, value_2013_a, value_2013_c, value_2014_a, value_2014_c ... ...
i've write following sql query not works. take first type
select agent,product, case when type='c' value_2013 else 0 end value_2013_c, <-- take value,ok! case when type='c' value_2013 else 0 end value_2013_a, <-- not take value case when type='a' impap else 0 end value_2014_a, <-- take value, ok! case when type='a' impac else 0 end value_2014_c <-- not take value mytable group agent,product;
how modify ?
you forgot make max. try that
select agent,product, max(case when type='c' value_2013 else 0 end) value_2013_c, max(case when type='c' value_2013 else 0 end) value_2013_a, max(case when type='a' impap else 0 end) value_2014_a, max(case when type='a' impac else 0 end ) value_2014_c mytable group agent,product;
Comments
Post a Comment