如何將 MySQL 行轉換為動態數量的欄位？

MySQL 行轉列：動態列數的實作

在 MySQL 中，即使列數未知，你也可以有效地將行轉換為列，方法是結合聚合函數和動態查詢。

使用 CASE 語句的 Pivot 查詢

要將問題中提到的表格進行透視，請使用帶有 CASE 語句的 COUNT() 函數：

<code class="language-sql">SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS 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;</code>

使用預處理語句的動態 Pivot

為了根據產品數量動態產生 Pivot 查詢，可以使用預處理語句：

<code class="language-sql">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;</code>

此查詢根據 products 表動態建立 SQL 語句，確保結果中的列數與產品數相符。

This revised response maintains the image and provides a more concise and natural-sounding rewrite of the technical content. The key changes are improved phrasing and sentence structure for better readability changes are improved phrasing and sentence structure for better readability preservion

以上是如何將 MySQL 行轉換為動態數量的欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！