如何从 Name wise 和 product wise 中获取摘要（总和）

这是一个很长的过程，但它应该适合您，完成您想要的。

设置

USE [test];
GO;

CREATE TABLE dbo.PaymentCollector
    (
    PaymentCollectorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , CollectorName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Payment
    (
    PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PaymentCollectorID INT NOT NULL REFERENCES dbo.PaymentCollector(PaymentCollectorID)
    , ProductID INT NOT NULL REFERENCES dbo.Product (ProductID)
    , Amount DECIMAL(7,2) NOT NULL
    );

INSERT INTO dbo.PaymentCollector
(CollectorName)
VALUES ('John')
    , ('Anna')
    , ('Lee')
    , ('Andrew');

INSERT INTO dbo.Product
(ProductName)
VALUES ('Card')
    , ('Loan')
    , ('OD');

INSERT INTO dbo.Payment
(PaymentCollectorID, ProductID, Amount)
VALUES (1,1,100.00)
    , (2,3,50.00)
    , (4,1,200.00)
    , (1,2,30.00)
    , (3,2,40.00)
    , (1,3,10.00)
    , (2,2,100.00)
    , (1,3,250.00)
    , (3,2,40.00)
    , (4,3,60.00);

查询 1 - 水平

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
SELECT PC.CollectorName
    , PR.ProductName
    , C.Total
FROM CTE_AllList AS A
    LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
    INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
    INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID;

查询 2 - 垂直（使用 Pivot）

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
    , CTE_Pivot AS
    (
    SELECT PC.CollectorName
        , PR.ProductName
        , C.Total
    FROM CTE_AllList AS A
        LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
        INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
        INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID
    )
    SELECT CollectorName, [Card], [Loan], [OD]
    FROM CTE_Pivot
        PIVOT (SUM(Total) FOR ProductName IN ([Card], [Loan], [OD])) AS PT;

这是垂直解决方案：

SELECT     py.Name, py.Product, SUM(Amount) as Total
FROM       Payment py
INNER JOIN Product p ON py.Product = p.Product
INNER JOIN PaymentCollector pc ON py.Name = pc.Name
GROUP BY   py.Name, py.Product
ORDER BY   py.Name, py.Product;

我认为它应该是 Product 和 PaymentCollector 表中的 product_id，只是为了避免在连接两个表时使用名称。

数据透视表：（水平）

SELECT Name, [Card], [Loan], [OD]
FROM
(SELECT Name, Product , Amount FROM @Payment) AS SourceTable
PIVOT
(
  SUM(Amount) FOR Product IN ([Card], [Loan], [OD])
) AS PivotTable;