注：要變列的行單元對應的數據須唯一，故在下面的子查詢中先對目標行單元進行了分組，以保證唯一性。
1，用case指定條件
SELECT f.UserName,
SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-10' THEN b.Amount ELSE 0 END) AS '2011-03-10',
SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-11' THEN b.Amount ELSE 0 END) AS '2011-03-11',
SUM(CASE CONVERT(VARCHAR(10),b.OrderDate,120) WHEN '2011-03-12' THEN b.Amount ELSE 0 END) AS '2011-03-12'
FROM bdAchievement a(NOLOCK)
JOIN bdOrder b(NOLOCK) ON a.OrderNo = b.OrderNo
JOIN frmuser f ON a.SalesStaff = f.Account
WHERE a.RootDepartmentID = 184 AND b.OrderDate >='2011-03-10'
GROUP BY F.UserName
2，用SQL SERVER2005提供的關鍵詞PIVOT來執行
行變列：將多行的值合並放到列上去，這就需要一個聚合函數，此外要指定拆分成行（列一行）的列並且此列按如何規則分配到行上去
SELECT * FROM (
SELECT f.UserName,CONVERT(VARCHAR(10),OrderDate,120) orderdate,b.Amount
FROM bdAchievement a(NOLOCK)
JOIN bdOrder b(NOLOCK) ON a.OrderNo = b.OrderNo
JOIN frmuser f ON a.SalesStaff = f.Account
WHERE a.RootDepartmentID = 3 AND b.OrderDate >='2010-02-10') O
PIVOT (SUM(amount) FOR orderdate IN([2011-03-10],[2011-03-11],[2011-03-12],[2011-03-13])) AS c
將列訂單日期轉換到行上去，分成多個列，規則是[2011-03-10],[2011-03-11],[2011-03-12],[2011-03-13])，並對要求的amount求和
同樣列變行為：UNPIVOT
注：應用限制，列拆分規則是有限的幾個，即行名是可指定的，若行名是未知數，那查出的數據將不知放到哪一列，故這樣的行轉列是則不可執行
注: PIVOT 與CASE 方法的區別：PIVOT簡潔，但只能將一個列的列值轉為列，沒有CASE靈活
3，將列直接轉成行
現有一客戶電話表，客戶電話最多4個，要求以行形式顯示出來
CREATE TABLE tmpTable(CustomerID INT,Tel VARCHAR(20))
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654321')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654322')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654323')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(222,'13587654324')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(333,'13587654331')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(333,'13587654332')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654341')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654332')
INSERT INTO tmpTable(CustomerID,Tel) VALUES(444,'13587654332')
SELECT * FROM (
SELECT customerid,tel, ROW_NUMBER () OVER ( PARTITION BY CustomerID ORDER BY tel DESC )r
FROM tmpTable tt)m
PIVOT (max(tel) FOR r IN ([1],[2],[3],[4]) ) AS t
