如何用SQL語句實現行列轉換

如何用SQL語句實現行列轉換

行列轉換是資料庫系統中經常遇到的一個需求，在資料庫設計時，為了適合資料的累積儲存，往往採用直接記錄的方式，而在展示資料時，則希望整理所有記錄並且轉置顯示。圖9.1展示了行列轉換的功能。

圖9.1  行列轉換的需求

分析這個需求，可以發現希望做的是找出具有相同部門的記錄，並根據其材料的值累加數量。如果手動來寫的話，最終希望得到的是下面這樣的SQL語句：

select 部門, sum(case 材料 when '材料1' then 數量 else 0 end) [材料1], sum(case 材料 when '材料2' then 數量 else 0 end) [材料2], sum(case 材料 when '材料3' then 數量 else 0 end) [材料3] from 部門耗材 group by 部門

這是一個非常簡單的查詢語句，並且執行結果恰好就是希望得到的結果，但問題是，如何得知原表中究竟包含幾種材料呢？顯然，根據上述的SQL語句，得到的結果永遠只能統計3種材料的消耗，這時候就需要動態地根據實際材料數目來得到查詢語句。代碼9-1實現了一個動態行列轉換。

代碼9-1  動態行列轉換： Transfer.sql

 

--申明一個字串變數，以供動態拼裝 declare @sql varchar(8000) --拼裝SQL命令 set @sql = 'select Department' --動態地獲得材料，為每個材料構建一個列 select @sql = @sql + ',sum(case Item when '''+Item+''' then Number else 0 end) ['+Item+']' from (select distinct Item from DepartCost) as a --最終加上選擇源和GROUP BY語句 select @sql = @sql+' from DepartCost group by Department' --執行SQL命令 exec(@sql)

 

為了書寫方便，表名和列名都沒有採用中文名字。建議讀者在進行資料庫設計時，盡量避免直接使用漢字，可以採用拼音或者縮寫的方式來替代。

下面是這個SQL命令的執行結果：

Department	Item1	Item2	Item3
F1	3	1	2
F2	0	2	1
F3	1	0	1

這樣的解決方案仍然有不少缺陷。主要有兩點：第一是動態SQL命令執行效率往往不高，因為動態拼裝的原因，導致資料庫管理系統無法對這樣的命令進行最佳化；第二是這樣的SQL命令必須先確定其長度限制，而動態SQL命令的長度往往根據實際表的內容而改變，所以這個命令無法保證100%能夠運行。  答案 行列轉換的SQL命令通常需要依靠動態SQL語句，具體的實現方法請參考本節的問題分析。