MYSQL create dynamic pivot table

Question

This my query

SELECT w.purchase_date, c.center_name, sum(w.final_kg) FROM wd_leaf_purchase w
join wd_leaves_supplier s on w.supplier_id = s.supplier_id
join wd_collection_center c on s.center_id = c.center_id
WHERE EXTRACT(MONTH FROM w.purchase_date) = MONTH(NOW())
AND EXTRACT(YEAR FROM w.purchase_date) = YEAR(NOW())
group by w.purchase_date, c.center_name;

Output of above query is

'2014-06-11', 'Main Center', '10.00'
'2014-06-20', 'Main Center', '10.00'
'2014-06-26', 'Main Center', '9.00'
'2014-06-26', 'Center B', '9.50'
'2014-06-27', 'Main Center', '73.00'
'2014-06-27', 'Center B', '73.00'
'2014-06-28', 'Main Center', '103.00'
'2014-06-28', 'Center B', '8.00'

But i need output like below.

'2014-06-11', 'Main Center', '10.00', 'Center B', '0'
'2014-06-20', 'Main Center', '10.00', 'Center B', '0'
'2014-06-26', 'Main Center', '9.00' 'Center B', '9.50'
'2014-06-27', 'Main Center', '73.00' 'Center B', '73.00'
'2014-06-28', 'Main Center', '103.00' 'Center B', '8.00'

How can write a query to get above output ?

Here is sqlfiddle

can you post the field names of the tables rather the table structures. — Abhik Chakraborty, Jun 29 '14 at 05:29
@AbhikChakraborty Here is the sqlfiddle url. http://sqlfiddle.com/#!8/523eb/1 — Bishan, Jun 29 '14 at 06:36

Fabricator · Accepted Answer · 2014-07-10T21:22:39.897

1

Use prepared statement to build up columns dynamically:

set @sql:=null;
select group_concat(distinct concat('sum(if(c.center_name="',center_name,'",w.final_kg,0)) `', center_name,'`') order by center_id)
from wd_collection_center
into @sql;

set @sql:=concat('SELECT w.purchase_date,', @sql, '
                  FROM wd_leaf_purchase w
                  join wd_leaves_supplier s on w.supplier_id = s.supplier_id
                  join wd_collection_center c on s.center_id = c.center_id
                  WHERE EXTRACT(MONTH FROM w.purchase_date) = MONTH(NOW())
                  AND EXTRACT(YEAR FROM w.purchase_date) = YEAR(NOW())
                  group by w.purchase_date;');

prepare st from @sql;
execute st;
deallocate prepare st;

fiddle

edited Jul 10 '14 at 21:22

answered Jun 29 '14 at 05:31

Fabricator

12,722
2
27
40

Unfortunately `center_name`s are dynamic. how can i do this in that situation ? – Bishan Jun 29 '14 at 06:22
@Bishan, in that case you'll need prepared statement to build up the query dynamically – Fabricator Jun 29 '14 at 06:26
Could you please explain how can i do that ? – Bishan Jun 29 '14 at 06:29
Here is the sqlfiddle url. http://sqlfiddle.com/#!8/523eb/1 – Bishan Jun 29 '14 at 06:35
Thank you very much :) i have another problem. Could you please tell me how can i use this code in java ? currently i'm executing query like this. `entityManager.getEntityManager().createNativeQuery("query here").getResultList();` – Bishan Jun 29 '14 at 07:03
@Bishan, sorry I've not used mysql with java before. This may be helpful: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Fabricator Jun 29 '14 at 07:18
It's ok :) I'll check your link. Thank you. – Bishan Jun 29 '14 at 07:20
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56516/discussion-between-bishan-and-fabricator). – Bishan Jun 30 '14 at 08:30
In above query how can i sort by center_id ? i have tried order by center_id. but didn't work. currently result is sorting by center_name. New [Fiddle](http://sqlfiddle.com/#!2/aa42d/1) – Bishan Jul 10 '14 at 07:52
@Bishan, you need to add `order by` in the group_concat statement. See updated answer. – Fabricator Jul 10 '14 at 21:23

MYSQL create dynamic pivot table

1 Answers1