RiCo技術農場

今天朋友有個樞鈕分析小需求，使用者希望知道每個月份物品個別數量和全部總量(結果如輸出表)

由於物品名稱不固定，導致樞紐資料行也不固定，想了想應該只能動態組SQL來解決。

 

 

來源資料表

 

結果輸出表

 

動態Pivot

declare @pvtheaders nvarchar(max),@pvtsql nvarchar(max),@sumsql nvarchar(max)
--@pvtheaders 動態組出樞紐資料行
--@sumsql     動態組出樞紐資料行相加
select @pvtheaders=isnull(@pvtheaders,'')+'['+color+'],',
@sumsql=ISNULL(@sumsql,'')+'+isnull(['+color+'],0)'
from ##mytbl
group by color
--移除 "," 最後一位字元
set @pvtheaders= left(@pvtheaders, len(@pvtheaders) - 1)
--移除 "+" 第一位字元
set @sumsql= right(@sumsql, len(@sumsql) - 1)
--動態PIVOT
set @pvtsql='select *,sum('+ @sumsql +') over (partition by [month]) Total from ##mytbl p
pivot
(
sum([count])
for color in ('+ @pvtheaders +')
) pvt' 
--執行 
exec sp_executesql @pvtsql

 

參考

使用 PIVOT 和 UNPIVOT

SET 字串問題