How to Pivot Rows into Dynamic Columns in MySQL?

MySQL: Convert the line data to a dynamic column

Problem description

Suppose we have the following three MySQL tables:

Product table (Products):

• <code>  id | name
     1 | 产品A
     2 | 产品B</code>

  Partners table (partners):

• Sales (Sales):

  <code>  id | name
     1 | 合作伙伴A
     2 | 合作伙伴B</code>

• The goal is to convert the line data in the sales table into dynamic columns, and the name represents different products. The expected output results are as follows:

  <code>  partners_id | products_id
              1             2
              2             5
              1             5
              1             3
              1             4
              1             5
              2             2
              2             4
              2             3
              1             1</code>

Answer

Unfortunately, MySQL lacks a special pivot function. However, we can combine the aggregate function and the case statement to achieve similar results:

<code>partner_name | 产品A | 产品B | 产品C | 产品D | 产品E
合作伙伴A              1           1           1           1           2
合作伙伴B              0           1           1           1           1</code>

Dynamic column conversion

In order to handle unknown products, we need to use dynamic columns to conversion. This needs to use the information in the product table to prepare the SQL statement:

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = '产品A' THEN 1 END) AS 产品A,
  COUNT(CASE WHEN pd.product_name = '产品B' THEN 1 END) AS 产品B,
  COUNT(CASE WHEN pd.product_name = '产品C' THEN 1 END) AS 产品C,
  COUNT(CASE WHEN pd.product_name = '产品D' THEN 1 END) AS 产品D,
  COUNT(CASE WHEN pd.product_name = '产品E' THEN 1 END) AS 产品E
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

This method allows the processing of any number of products, and ensure that the inquiry can adapt to changes in the product table without modifying the SQL statement itself.

The above is the detailed content of How to Pivot Rows into Dynamic Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!