SQL 查询用于在 SQL Server 中生成类似输出查询相关表的矩阵

Question

我有三张桌子：
产品

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 中实现上述结果。使用多个视图甚至临时表对我来说都很好。

Answer 1

您可以使用 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

Answer 2

我们可以使用主元创建矩阵，
这可以通过数据框轻松完成

product|Key|Value
A      |P  |10|
A      |Q  |40|
B      |R  |50|
B      |S  |50|

val newdf=df.groupBy("product").pivot("key").sum("value")

|product|P   |Q   |R   |S   |
|B      |null|null|  50|  50|
|A      |  10|  40|null|null|

我们可以替换 null 并且我们也可以进行计算

Answer 3

create table #Product (ProductID   int,ProductName  varchar(15))
insert into #Product values (1,'Cycle')
insert into #Product values (2,'Scooter')
insert into #Product values (3,'Car')

create table #Customer (CustomerID   int, CustomerName  varchar(30))
insert into #Customer values (101,'Ronald')
insert into #Customer values (102,'Michelle')
insert into #Customer values (103,'Armstrong')
insert into #Customer values (104,'Schmidt')
insert into #Customer values (105,'Peterson')

create table #Transactions (TID int,ProductID int,CustomerID int, TranDate smalldatetime,Amount decimal(18,2))
insert into #Transactions values (10001,1,101,'01-Jan-11',25000.00)
insert into #Transactions values (10002,2,101,'02-Jan-11',98547.52)
insert into #Transactions values (10003,1,102,'03-Feb-11',15000.00)
insert into #Transactions values (10004,3,102,'07-Jan-11',36571.85)
insert into #Transactions values (10005,2,105,'09-Feb-11',82658.23)
insert into #Transactions values (10006,2,104,'10-Feb-11',54000.25)
insert into #Transactions values (10007,3,103,'20-Feb-11',80115.50)
insert into #Transactions values (10008,3,104,'22-Feb-11',45000.65)

with temp as 
(
  select cus.CustomerName,pro.ProductName, sum(trans.Amount) as Amount 
  FROM #Transactions as trans
  inner join #Customer as cus on trans.CustomerID = cus.CustomerID
  inner join #Product as pro on trans.ProductID = pro.ProductID
  group by cus.CustomerName,pro.ProductName
)

select 
  CustomerName
  , isnull([Car],0) as Car
  , isnull([Cycle],0) as Cycle
  , isnull([Scooter],0) as Scooter
  , isnull([Car],0) + isnull([Cycle],0) + isnull([Scooter],0) as Total 
  FROM temp
pivot (
  SUM(Amount) for ProductName in ([Cycle],[Scooter],[Car])
) as pot