我有三个表： 产品
ProductID ProductName 1 Cycle 2 Scooter 3 Car
顾客
CustomerID CustomerName 101 Ronald 102 Michelle 103 Armstrong 104 Schmidt 105 Peterson
交易次数
TID ProductID CustomerID TranDate Amount 10001 1 101 01-Jan-11 25000.00 10002 2 101 02-Jan-11 98547.52 10003 1 102 03-Feb-11 15000.00 10004 3 102 07-Jan-11 36571.85 10005 2 105 09-Feb-11 82658.23 10006 2 104 10-Feb-11 54000.25 10007 3 103 20-Feb-11 80115.50 10008 3 104 22-Feb-11 45000.65
我写了一个查询来对事务进行分组，如下所示：
SELECT P.ProductName AS Product, C.CustName AS Customer, SUM(T.Amount) AS Amount FROM Transactions AS T INNER JOIN Product AS P ON T.ProductID = P.ProductID INNER JOIN Customer AS C ON T.CustomerID = C.CustomerID WHERE T.TranDate BETWEEN '2011-01-01' AND '2011-03-31' GROUP BY P.ProductName, C.CustName ORDER BY P.ProductName
结果如下：
Product Customer Amount Car Armstrong 80115.50 Car Michelle 36571.85 Car Schmidt 45000.65 Cycle Michelle 15000.00 Cycle Ronald 25000.00 Scooter Peterson 82658.23 Scooter Ronald 98547.52 Scooter Schmidt 54000.25
我需要像这样的MATRIX形式的查询结果：
Customer |------------ Amounts --------------- Name |Car Cycle Scooter Totals Armstrong 80115.50 0.00 0.00 80115.50 Michelle 36571.85 15000.00 0.00 51571.85 Ronald 0.00 25000.00 98547.52 123547.52 Peterson 0.00 0.00 82658.23 82658.23 Schmidt 45000.65 0.00 54000.25 99000.90
请帮助我在SQL Server 2005中获得以上结果。使用多视图甚至临时表对我来说都很好。
您可以使用SQL Server的PIVOT运算符
SELECT * FROM ( SELECT P.ProductName , C.CustName , T.Amount FROM Transactions AS T INNER JOIN Product AS P ON T.ProductID = P.ProductID INNER JOIN Customer AS C ON T.CustomerID = C.CustomerID WHERE T.TranDate BETWEEN '2011-01-01' AND '2011-03-31' ) s PIVOT (SUM(Amount) FOR ProductName IN ([Car], [Cycle], [Scooter])) pvt
测试数据
;WITH q AS ( SELECT [Product] = 'Car', [Customer] = 'Armstrong', [Amount] = 80115.50 UNION ALL SELECT 'Car', 'Michelle', 36571.85 UNION ALL SELECT 'Car', 'Schmidt', 45000.65 UNION ALL SELECT 'Cycle', 'Michelle', 15000.00 UNION ALL SELECT 'Cycle', 'Ronald', 25000.00 UNION ALL SELECT 'Scooter', 'Peterson', 82658.23 UNION ALL SELECT 'Scooter', 'Ronald', 98547.52 UNION ALL SELECT 'Scooter', 'Schmidt', 54000.25 ) SELECT Customer , Car = ISNULL(Car, 0) , Cycle = ISNULL(Cycle, 0) , Scooter = ISNULL(Scooter, 0) , Total = ISNULL(Car, 0) + ISNULL(Cycle, 0) + ISNULL(Scooter, 0) FROM ( SELECT * FROM q ) s PIVOT (SUM(Amount) FOR Product IN ([Car], [Cycle], [Scooter])) pvt
输出
Customer Car Cycle Scooter Total Armstrong 80115.50 0.00 0.00 80115.50 Michelle 36571.85 15000.00 0.00 51571.85 Peterson 0.00 0.00 82658.23 82658.23 Ronald 0.00 25000.00 98547.52 123547.52 Schmidt 45000.65 0.00 54000.25 99000.90
