對這文章發表回應
發表限制: 非會員 可以發表
[SQL指令]使用 PIVOT 「扭轉」查詢的結果。把SQL查詢成果(直式),轉成Excel常用的(橫式)展示格式
想把橫向列資料改成多欄資料時,該怎麼作?
資料合併與轉向 / PIVOT 和 UNPIVOT。
假設我有一個 Table,記錄了每一個月、各分店的銷售金額(或是銷售量)
這樣的 資料庫 Table如果要作年度的匯總運算,
很可能查出來的結果會是這個樣子。
年度 | 銷售量 |
2010 | 100,000 |
2009 | 80,000 |
2008 | 50,000 |
畢竟,Table裡面的每一列記錄,都是橫的(一列、一列)
透過 Group by或是 Count()計算之後,成果也必然是這樣。
但,有時候要產生這樣的報表,就很傷腦筋了。
偏偏大部分的 User滿喜歡這種輸出成果。
2008 | 2009 | 2010 |
50,000 | 80,000 | 100,000 |
如果您聽不懂我的敘述,請看以下的「第一篇 推薦文章(張小呆 的大作)」,有圖片解說。
新版本的 MS SQL 2005開始,多了新方法來處理這樣「資料表的結果 "逆轉"」
簡單的說,把原本(上圖一)的年度「欄位(Column)」,扭轉(轉向)成一列「資料列 (Row / 一筆紀錄)」
原廠的說明文件如下: http://msdn.microsoft.com/zh-tw/library/ms177410.aspx
使用 PIVOT 和 UNPIVOT
您可以使用 PIVOT 和 UNPIVOT 關係運算子,將資料表值運算式變更為另一個資料表。PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行,以 旋轉資料表值運算式,然後依據最終輸出的需要,對其餘的任何資料行值執行必要的彙總。UNPIVOT 執行的作業則與 PIVOT 相反,它會將資料表值運算式旋轉為資料行值。
Sorry......上面的說明的確很像火星文
但有三篇文章講得更淺顯易懂(我推薦這三篇文章):
張小呆 http://www.dotblogs.com.tw/dc690216/archive/2010/02/04/13478.aspx...... 圖文並茂,很讚喔!
Rely1020 http://rely1020.blog.ithome.com.tw/post/1606/39111......把語法與理論講得很清楚。
看完上面兩篇文章的解釋,您多看一個範例(練習、比對一下),就會懂了。
黑暗執行緒
http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/07/20/tips-using-pivot-in-sql-2005.aspx
---------------------------------------------------------------------------------------------------------------------------
2011/7/7 補充:
假設原本的 Table是這個樣子:
我們透過 PIVOT來扭轉輸出的成果:
上圖的這種格式,比較適合 Excel常用的展示格式,或是透過 excel繼續運算或是繪圖。
---------------------------------------------------------------------------------------------------------------------------
如果您不想使用(或是無法使用這種方法的話)
這本書的附錄 A也有另外一種解法。請看文章標題 -- 「想把 橫向列資料改成 多欄資料時,該怎麼作?」頁數533
但我覺得這方法不如PIVOT 和 UNPIVOT好用
書名:SQL語法範例辭典(旗標出版社)
作者:朝井 淳
另外,楊志強老師在 旗標出版社推出的「T-SQL 實戰學堂」一書,
裡面的 Ch .6-4節(資料合併與轉向)也有解說 PIVOT 和 UNPIVOT。
如果不用 PIVOT的話,這裡也有指導您其他的寫法。(不是我寫的書,不方便直接COPY指令給大家看 Sorry!)
2011/10/5補充:
請看下一篇相關文章:
[SQL指令]使用 UNPIVOT,橫式結果「扭轉」成直式。(下集,投票區 + Chart 控制項)
原文出處:[SQL指令]使用 PIVOT 「扭轉」查詢的結果。把SQL查詢成果(直式),轉成Excel常用的(橫式)展示格式 | ASP.NET專題實務 教學影片 入門實戰課程 -- MIS2000Lab. - 點部落