Lets say I have three different MySQL tables:
Table products
:
id | name 1 Product A 2 Product B
Table partners
:
id | name 1 Partner A 2 Partner B
Table sales
:
partners_id | products_id 1 2 2 5 1 5 1 3 1 4 1 5 2 2 2 4 2 3 1 1
I would like to get a table with partners in the rows and products as columns. So far I was able to get an output like this:
name | name | COUNT( * ) Partner A Product A 1 Partner A Product B 1 Partner A Product C 1 Partner A Product D 1 Partner A Product E 2 Partner B Product B 1 Partner B Product C 1 Partner B Product D 1 Partner B Product E 1
Using this query:
SELECT partners.name, products.name, COUNT( * ) FROM sales JOIN products ON sales.products_id = products.id JOIN partners ON sales.partners_id = partners.id GROUP BY sales.partners_id, sales.products_id LIMIT 0 , 30
but I would like to have instead something like:
partner_name | Product A | Product B | Product C | Product D | Product E Partner A 1 1 1 1 2 Partner B 0 1 1 1 1
The problem is that I cannot tell how many products I will have so the column number needs to change dynamically depending on the rows in the products table.
This very good answer does not seem to work with mysql: T-SQL Pivot? Possibility of creating table columns from row values
Unfortunately MySQL does not have a PIVOT
function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE
statement:
select pt.partner_name, count(case when pd.product_name = 'Product A' THEN 1 END) ProductA, count(case when pd.product_name = 'Product B' THEN 1 END) ProductB, count(case when pd.product_name = 'Product C' THEN 1 END) ProductC, count(case when pd.product_name = 'Product D' THEN 1 END) ProductD, count(case when pd.product_name = 'Product E' THEN 1 END) ProductE from partners pt left join sales s on pt.part_id = s.partner_id left join products pd on s.product_id = pd.prod_id group by pt.partner_name
See SQL Demo
Since you do not know the Products you will probably want to perform this dynamically. This can be done using prepared statements.
With dynamic pivot tables (transform rows to columns) your code would look like this:
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'count(case when Product_Name = ''', Product_Name, ''' then 1 end) AS ', replace(Product_Name, ' ', '') ) ) INTO @sql from products; SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' from partners pt left join sales s on pt.part_id = s.partner_id left join products pd on s.product_id = pd.prod_id group by pt.partner_name'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
See SQL Demo
It's probably worth noting that GROUP_CONCAT
is by default limited to 1024 bytes. You can work around this by setting it higher for the duration of your procedure, ie. SET @@group_concat_max_len = 32000;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With