如何在SQL中產生交叉式資料表
在SQL 2005以後，可以用新的方式PIVOT來進行相同的功能
請參考以下這篇
http://www.dotblogs.com.tw/topcat/archive/2008/04/14/2910.aspx
偶們在Access中，如果有以下的資料
Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
偶們需要把其中的Quarter放成欄位時，偶們可以用查詢中的
交*資料表查詢精靈來達到偶們的要求，可素，粉可惜的素
在SQL中卻沒有這樣的精靈可以用!
Year Q1 Q2 Q3 Q4
--------------------------------------
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
偶本來素用一個蘿蔔一個坑的方式，在ASP中處理，但是這樣的程式跑起來
要粉久的ㄋㄟ......
因為需要好幾層的迴圈........
後來偶找到了一個方式可以處理，那就素：
SELECT Year
,SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1
,SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2
,SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3
,SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
其中的【,SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1】
就素關鍵啦.....
找到個鍋東東後，現在的問題素....如果偶的Quarter資料素變動的，要怎麼做ㄋㄟ....
因為這樣的語法偶們可以在VB中組合出來，
當偶們要組合這樣的語法前，偶們可以先用群組的方式把所有的可能產生一個rs
然後就素
===========================================
SQLTXT = "SELECT Year"
rs.MoveFirst
Do
SQLTXT = SQLTXT & " ,SUM(CASE Quarter WHEN " & rs("GQ") & " THEN Amount ELSE 0 END) AS '" & rs("GQ") & "'"
rs.MoveNext
Loop Until rs.EOF
SQLTXT = SQLTXT & "FROM Northwind.dbo.Pivot GROUP BY Year"
=============================================
如此就能夠產生偶們所要的資料啦
最後，在ASP中，只須把這樣的RS給秀出來就成啦
希望這鍋東東對大家有用處
^_^
--------------------------------------------------------------------------------
以下是SQL說明檔中的說明內容：
--------------------------------------------------------------------------------
以下素中文版的說明
===============================================================================================
有時候您需要旋轉 (Rotate) 結果，以便讓資料行以水平方向排列，資料列以垂直方向排列。這是所謂的建立樞紐分析表 (PivotTable)R、建立交*定位報表 (Cross-tab Report) 或旋轉資料 (Rotating Data)。
假設有一個資料表 Pivot ，每一個資料列代表一季。Pivot 的 SELECT 陳述式將以垂直方向來產生季報表：
Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
某個報表必須以包含一年一列的資料表來產生，而每季的數值出現在個別的資料行內，例如：
Year
Q1
Q2
Q3
Q4
1990
1.1
1.2
1.3
1.4
1991
2.1
2.2
2.3
2.4
下列陳述式可用來建立 Pivot 資料表，並填入第一個資料表的資料：
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO下列 SELECT 陳述式可用來建立旋轉的結果：
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO這個 SELECT 陳述式也可處理每季包含多個資料列的資料表。GROUP BY 會將 Pivot 內指定年份的所有資料列合併成輸出中的一個資料列。執行群組作業時，會套用 SUM 彙總中的 CASE 函數，以便讓每季 Amount 數值加到結果集中適當的資料行內，並將 0 加到其他季的結果集資料行內。
若以此 SELECT 陳述式的結果做為試算表的輸入，試算表將可輕鬆地算出每一年的加總。若應用程式使用了 SELECT 陳述式，您可輕鬆地修改 SELECT 陳述式，讓它計算每年的加總。例如：
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO不管是 GROUP BY 加上 CUBE，或是 GROUP BY 加上 ROLLUP
以下是簽名：
- 歡迎轉貼本站的文章，不過請在貼文主旨上加上【轉貼】，並在文章中附上本篇的超連結與站名【topcat姍舞之間的極度凝聚】，感恩大家的配合。
- 小喵大部分的文章會以小喵熟悉的語言VB.NET撰寫，如果您需要C#的Code，也許您可以試著用線上的工具進行轉換，這裡提供幾個參考
| Microsoft MVP Visual Studio and Development Technologies (2005~2019/6) | topcat Blog:http://www.dotblogs.com.tw/topcat |