SETSESSION GROUP_CONCAT_MAX_LEN = 10000000;
SET @sql = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('SUM(CASE WHEN c.area = "', c.area,'" AND ',
(CASEWHEN c.total_area ISNOTNULLTHENCONCAT('c.total_area = ',c.total_area)
ELSENULLEND),
' THEN c.total_area else 0 end) AS ','"',
c.shop_name,'_',c.area,'"'
)
)
INTO @sqlFROM (SELECT b.shop_name,b.area,COUNT(b.area) total_area
FROM drink_shop b
WHERE b.shop_name ISNOTNULLGROUPBY b.shop_name,b.area) c;
SET @sql = CONCAT('SELECT c.shop_name, ', @sql,
' FROM (SELECT b.shop_name,b.area,
COUNT(b.area) total_area
FROM drink_shop b
WHERE b.shop_name IS NOT NULL
GROUP BY b.shop_name,b.area) c
GROUP BY c.shop_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;
SELECT
c.shop_name,
SUM(CASEWHEN c.area='台北'then c.total_area ELSE0END) AS column_taipei,
SUM(CASEWHEN c.area='桃園'then c.total_area ELSE0END) AS column_taoyuan,
SUM(CASEWHEN c.area='新北'then c.total_area ELSE0END) AS column_newtaipei
FROM (SELECT b.shop_name,b.area,COUNT(b.area) total_area
FROM drink_shop b
WHERE1=1AND b.shop_name ISNOTNULLGROUPBY b.shop_name,b.area) c
GROUPBY c.shop_name;