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

Popular posts from this blog

Android layout hidden on keyboard show -

google app engine - 403 Forbidden POST - Flask WTForms -

c - Why would PK11_GenerateRandom() return an error -8023? -