By FeeJai


2012-08-17 11:06:46 8 Comments

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

1 comments

@Taryn 2012-08-17 12:19:15

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 Fiddle with Demo

Since you do not know the Products you will probably want to perform this dynamically. This can be done using prepared statements.

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 Fiddle with 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;

@Edward 2013-11-13 17:49:55

The comment at the end regarding "max_len" was a lifesaver! Thanks for the tip.

@Boynux 2013-11-29 03:14:04

if you are looking for more dynamic pivot queries please check this: boynux.com/creating-pivot-reports-in-mysql

@avatarofhope2 2019-04-18 19:29:53

What happens if the product name is ProductA') from partners pt; truncate partners;

Related Questions

Sponsored Content

43 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

8 Answered Questions

[SOLVED] Convert Rows to columns using 'Pivot' in SQL Server

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

7 Answered Questions

7 Answered Questions

[SOLVED] SQL Server query - Selecting COUNT(*) with DISTINCT

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

37 Answered Questions

8 Answered Questions

[SOLVED] MySQL pivot table

  • 2011-10-06 13:20:14
  • peku
  • 284957 View
  • 279 Score
  • 8 Answer
  • Tags:   mysql pivot

Sponsored Content