This sql statement works only if a store_location value is a single word, and doesn't work for-CodePudding

I have these SQL statements below for generating a pivot table.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(
  CASE WHEN store_location = "', store_location, '" THEN num_sales ELSE 0 END) 
  AS ', store_location)
)
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;

It works in the table below, in which store_location is a single word.

product_name	store_location	num_sales
Chair	North	17
Desk	West	30
Desk	North	44
Chair	South	20
Desk	East	25
Chair	East	20
Desk	South	25
Chair	West	13

But does not work for the table below, in this case store_location has multiple words.

product_name	store_location	num_sales
Chair	North City	17
Desk	West City	30
Desk	North City	44
Chair	South City	20
Desk	East City	25
Chair	East City	20
Desk	South City	25
Chair	West City	13

I have tried to use backticks around store_location in the SQL statement, but still, it does not work.

It gives this error

#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

CodePudding user response：

Using backticks works for me:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(
  CASE WHEN store_location = "', store_location, '" THEN num_sales ELSE 0 END) 
  AS `', store_location, '`')
)
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;

Check the demo here.