SQL Server 中的 PIVOT 列及其 SUM

参考我之前的

所以在这里您可以看到顶部的原始数据透视表，它被包裹在一个名为 ProductQuantities 的通用表表达式 (CTE) 中.然后我们为付款添加了一个新的 CTE，您会注意到它的 Group By 略有不同，因为我们只对客户的现金付款感兴趣.最后，我们将针对客户的两个查询的结果连接在一起，以获得我们的最终结果集.

SET @query = N'WITH ProductQuantities As (SELECT cus_Name,'+ @colsForSelect +'从 (选择 cus_Name, prod_Name, SUM(ord_Qty) 作为 sum_ord_Qty从订单 oc.cus_ID = o.cus_ID 上的内部连接客户 cp.prod_ID = o.Prod_ID 上的内连接产品 pGROUP BY cus_Name, prod_Name) pPIVOT (MAX([sum_ord_Qty]) FOR prod_Name IN ('+ @colsForPivot +'))AS pvt),客户付款方式 (选择 cus_Name, SUM(Case When ord_PurchaseType = ''cash'' then p.prod_Price * o.[ord_Qty] else 0 End) 作为 [Cash Payments]从订单 oc.cus_ID = o.cus_ID 上的内部连接客户 cp.prod_ID = o.Prod_ID 上的内连接产品 pGROUP BY cus_Name)选择 pq.*, cp.[现金支付]FROM ProductQuantities pq内部联接 CustomerPayments cp on pq.cus_Name = cp.cus_Name'

In reference to my previous Question . With these changes in the tables : (*) are used to mark the changes in the tables.

Products Table

prod_ID   - int
prod_Name - varchar(100)
prod_Code - varchar(100)
*prod_Price- float 

Orders Table

ord_ID  - int
ord_Qty - int
prod_ID - int
cus_ID  - float
*ord_PurchaseType - varchar(100)

Given that all items are priced as 10 dollars each. I want to make the final report to be something like this.

Currently, I'm using something like this: SUM(CASE WHEN ord_PurchaseType = 'cash' THEN prod_Price ELSE 0 END) to get the data, It works when I run in not inside the SET @query = N' ... query. Aside from that It is having an error saying that it has an invalid column named cash. I also tried putting the word cash in a variable but it still produces the same error.

解决方案

Okay this one was a bit more challenging but building on our last answer. The difference with what you want to do here is essentially aggregate 2 different values into the same report. Summing the product quantities and also summing the cash payments. You can't do it all in a single pivot statement, but you can do them separately and then join them together.

See SQL Fiddle

So here you see our original Pivot at the top which has been wrapped in a Common Table Expression (CTE) called ProductQuantities. We have then added a new CTE for the payments which you will notice has a slightly different Group By as we are only interested in the Cash Payments by Customer. Finally we join the results of both queries together on the customer to get our final result set.

SET @query = N'WITH ProductQuantities As (
SELECT cus_Name,'+ @colsForSelect +' 
FROM (    
     Select cus_Name, prod_Name, SUM(ord_Qty) as sum_ord_Qty
     from Orders o
     inner join Customers c on c.cus_ID = o.cus_ID
     inner join Products p on p.prod_ID = o.Prod_ID
     GROUP BY cus_Name, prod_Name
) p 
PIVOT (MAX([sum_ord_Qty]) FOR prod_Name IN ( '+ @colsForPivot +' )) 
AS pvt),

CustomerPayments As (
Select cus_Name, SUM(Case When ord_PurchaseType = ''cash'' then p.prod_Price * o.[ord_Qty] else 0 End) as [Cash Payments]
     from Orders o
     inner join Customers c on c.cus_ID = o.cus_ID
     inner join Products p on p.prod_ID = o.Prod_ID
     GROUP BY cus_Name
)

Select pq.*, cp.[Cash Payments]
FROM ProductQuantities pq
INNER JOIN CustomerPayments cp on pq.cus_Name = cp.cus_Name'