How to convert this mysql table into pivot table?

Question

I have a table that look like this.

ID_ACTIVITY    ID_CLASS    EXEC_VALUE
    ACT_III           A           450
      ACT_I           B           550
     ACT_II           A           750
    ACT_III           B           550
     ACT_II           A           650
      ACT_I           A           750
      ACT_I           B           750
    ACT_III           A           950
    ACT_III           A           150

I'd like to create a report based on this table into some kind of pivot table. This should be the report output

ID_ACTIVITY     A_COUNT     B_COUNT    AB_SUM
      ACT_I           1           2      2050             
     ACT_II           2        NULL      1500
    ACT_III           3           1      2100

Note :

A_COUNT and B_COUNT are dynamic based on the value in ID_CLASS
AB_SUM is based on the value of EXEC_VALUE in each ID_ACTIVITY in each ID_CLASS

I have done it using PHP, but I wonder is it possible do it in MySQL way and is it faster to generate with around thousand of data?

Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — Shanteshwar Inde, Nov 28 '19 at 07:43
Well, I'd be tempted to some appliication code for that - e.g. php — Strawberry, Nov 28 '19 at 10:41

score 1 · Accepted Answer · answered Nov 28 '19 at 07:46

1

You can do conditional aggregation:

select 
    id_activity,
    sum(case when id_class = 'A' then 1 end) a_count,
    sum(case when id_class = 'B' then 1 end) b_count,
    sum(exec_value) ab_sum
from mytable
group by id_activity

answered Nov 28 '19 at 07:46

GMB

216,147
25
84
135

hi @GMB, if there is a new `ID_CLASS` should I add another condition? or somehow can do it automatically? – Shota Nov 28 '19 at 07:49
@Shota: no you will need to do it manually by adding another column to the query, with the correct conditional expression (`sum(case ... end)`). For this to be *automatic*, you would need to use dynamic SQL, which is a different beast (and beyond the scope of your initial question). – GMB Nov 28 '19 at 07:52

How to convert this mysql table into pivot table?

1 Answers1