[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 控制項)

 

 

 

我將思想傳授他人, 他人之所得,亦無損於我之所有;

猶如一人以我的燭火點燭,光亮與他同在,我卻不因此身處黑暗。----Thomas Jefferson

線上課程,遠距教學 (Web Form 49hr)  https://dotblogs.com.tw/mis2000lab/2016/02/01/aspnet_online_learning_distance_education_VS2015

線上課程,遠距教學 (ASP.NET MVC 75hr)  https://dotblogs.com.tw/mis2000lab/2018/08/14/ASPnet_MVC_Online_Learning_MIS2000Lab

ASP.NET MVC線上課程 第一天 免費看 (5.5小時) 

寫信給我,不要私訊 --  mis2000lab (at) yahoo.com.台灣  或  school (at) mis2000lab.net


ASP.NET遠距教學、線上課程(Web Form + MVC)。 第一天課程, "完整" 試聽。 

................   facebook社團   https://www.facebook.com/mis2000lab   ......................

................  YouTube (ASP.NET) 線上教學影片  https://www.youtube.com/channel/UC6IPPf6tvsNG8zX3u1LddvA/

 

Blog文章 "附的範例" 無法下載,請看 這裡 ...... https://dotblogs.com.tw/mis2000lab/2016/03/14/2008_2015_mis2000lab_sample_download

請看我們的「售後服務」範圍(嚴格認定)

......................................................................................................................................................

...................................................................................................................................................... 

[遠距教學、教學影片] ASP.NET (Web Form) 課程 上線了!MIS2000Lab.主講

事先錄製好的影片,並非上課時側錄!   觀看影片時,有如我「一對一」跟您面對面講課

 

ASP.NET MVC 5 線上教學

累積時數約 75小時...... 第一天(5.5小時)完整內容,"免費"讓您評估