MySQL动态透视表SQL报错求助：多词store_location无法运行

问题场景 #

原动态SQL用于生成透视表，当store_location字段值为单个单词时运行正常，但值为多单词（如North City）时触发语法错误，报错信息如下：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'City,SUM(
  CASE WHEN store_location = "North City" THEN num_sales ELSE 0 END) ' at line 3

问题原因 #

报错的核心是多单词的字段别名未被正确包裹：当store_location的值包含空格时，直接作为别名会被MySQL解析成两个独立的标识符（比如North和City），导致语法逻辑断裂。

解决方案 #

在拼接别名时，用反引号（）将store_location`的值包裹起来，确保MySQL将其识别为单个完整的别名。修正后的完整代码如下：

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(
  CASE WHEN store_location = "', store_location, '" THEN num_sales ELSE 0 END) 
  AS `', store_location, '`'
) SEPARATOR ', ')
INTO @sql
FROM product_sales;
 
SET @sql = CONCAT('SELECT product_name, ', @sql, 
  ' FROM product_sales GROUP BY product_name');
SELECT @sql;
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键修正点 #

1. 在AS后添加反引号：将AS ', store_location)改为AS ', store_location, '``，确保多单词别名被正确识别
2. 显式指定GROUP_CONCAT的分隔符为, （可选，但能让生成的SQL更易读）

验证效果 #

修正后生成的SQL中，多单词的别名会被反引号包裹，比如：

SELECT product_name, SUM(CASE WHEN store_location = "North City" THEN num_sales ELSE 0 END) AS `North City`, ... FROM product_sales GROUP BY product_name

此时MySQL能正确解析别名，透视表生成正常。

内容的提问来源于stack exchange，提问作者kelvin daniel