林可可程式語言共享與討論~

最近有一個需球要將資料用v-chart畫出堆疊圖，但資料sql table原本是長這樣

要轉成樞紐資料，變成這樣

接著用sql語法組動態產生的話會需要這樣寫

SET SESSION  GROUP_CONCAT_MAX_LEN = 10000000;
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT 
		   CONCAT('SUM(CASE WHEN c.area = "', c.area,'" AND ',
			(CASE WHEN c.total_area IS NOT NULL
			THEN CONCAT('c.total_area = ',c.total_area)
			ELSE NULL END),
			' THEN c.total_area else 0 end) AS ','"',
            c.shop_name,'_',c.area,'"'
			)
		)
INTO @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;
				
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;
DEALLOCATE PREPARE stmt;

這邊有一個很重要的點!!

如果你GROUP_CONCAT組出來的字串很長一定要先設定GROUP_CONCAT_MAX_LEN的長度

預設是1024個字元，一開始我沒注意到一直ERROR，後來SELECT出來看才知道超過1024結果字元都被截斷...

上面是動態產生適合資料會變動並且很多的時候

如果你要輸出的欄位沒有很多其實可以用下面的方法

SELECT
    c.shop_name,  
    SUM(CASE WHEN c.area='台北' then c.total_area ELSE 0 END) AS column_taipei,
    SUM(CASE WHEN c.area='桃園' then c.total_area ELSE 0 END) AS column_taoyuan,
    SUM(CASE WHEN c.area='新北' then c.total_area ELSE 0 END) AS column_newtaipei
FROM (SELECT b.shop_name,b.area,COUNT(b.area) total_area 
		FROM drink_shop b
		WHERE 1=1
		AND b.shop_name IS NOT NULL
		GROUP BY b.shop_name,b.area) c
GROUP BY c.shop_name;

搞了一下才瞭解SQL其實可以很好玩