用于生成矩阵的SQL查询，例如与SQL Server中的输出查询相关表类似的矩阵 [英] SQL Query for generating matrix like output querying related table in SQL Server

问题描述

我有三个表:
产品

I have three tables:
Product

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  

我写了一个查询来对事务进行分组，如下所示:

I have written a query to group the transactions like this:

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中获得以上结果.使用多视图甚至临时表对我来说都是很好的.

Please help me to acheive the above result in SQL Server 2005. Using mulitple views or even temporory tables is fine for me.

推荐答案

您可以使用SQL Server的

You can use SQL Server's PIVOT operator

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

这篇关于用于生成矩阵的SQL查询，例如与SQL Server中的输出查询相关表类似的矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！