2018年7月21日 星期六

[SQL] 使用pivot進行資料轉置

之前在工作上接觸過有關問卷的案子

需要把資料轉置後輸出

以我去年紀錄的虛擬股票交易資料來做說明

如圖,2017/12/01 交易四張股票,分別為1229、4915、5234、6147


這段時間的交易想整理一下,把上方標題列改成股票代碼,左邊為交易日期,如下圖


直接從1216這隻股票代碼看下來,12/01 ~ 12/06都沒有交易

1229 12/01 ~ 12/05 各有一筆交易紀錄

用這樣的方式,更清楚地呈現每支股票的交易情況

今天介紹的,就是使用SQL的pivot來進行像這樣資料轉置



先來解答,在一步一步解釋吧,程式碼如下

declare @cols nvarchar(max)
declare @sql nvarchar(max)

--步驟1:組合樞紐資料行
set @cols = stuff((select distinct ',' + quotename(id) from business for xml path(''),TYPE)
.value('.','nvarchar(max)'),1,1,'');

--步驟2:組合樞紐SQL
set @sql = 'select date ,'+@cols +'
from (
select date,id
from business
) x pivot (
count(id) for id in ('+@cols+')
) p';

execute(@sql)

宣告兩個字串這就不用說了吧

首先第一步,先把需要統計的標題列字串串起來

以本文章案例而言,就是那些股票代碼,1216, 1229, 1526, 1710 ... 等等,語法如下

set @cols = stuff((select distinct ',' + quotename(id) from business for xml path(''),TYPE).value('.','nvarchar(max)'),1,1,'');

印出結果如下




第二步驟,就是把要統計彙整的sql組合起來

本案例的統計方式是計算個數,所以用count

最後再執行組好的SQL就可以拉
execute(@sql)

參考資料:


沒有留言:

張貼留言