问题描述
是否可以在SQL Server中对多个聚合的列进行数据透视?我有以下订单表:
Is it possible to pivot on multiple aggregated columns in SQL Server? I have the following order table:
[ORDERS]
| CustName | OrderedProduct | QtyOrdered | UnitCost | UnitPrice | OrderDate
Bob | Canned Tuna | 6 | 11 | 14 | 21-12-2016 13:11:00
Steve | Canned Salmon | 2 | 15 | 19 | 03-11-2016 11:03:00
Bob | Canned Tuna | 10 | 10 | 13 | 22-12-2016 10:43:00
Bob | Canned Tuna | 4 | 9 | 10 | 13-11-2016 17:22:00
我有以下数据查询,该查询为我提供了11月/12月每个客户每个产品的订购数量:
I have the following pivot query which gives me an order quantity per product per customer for months Nov/Dec:
SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016] FROM
(
SELECT CustName, OrderedProduct, QtyOrdered,
'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
FROM [ORDERS]
) src
PIVOT
(
SUM(QtyOrdered)
FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol
WHERE CustName='Bob'
这给出了预期的结果
CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016
Bob | Canned Tuna | 16 | 4
我还希望以边距为中心,因此将 src 查询更改为:
What I want is to also pivot on margin so changing the src query to:
SELECT CustName, OrderedProduct, QtyOrdered,
((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
FROM [ORDERS]
我尝试了以下修改后的查询,添加了第二个枢轴
I have tried the following modified query adding a second pivot
SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016], [MAR Dec 2016], [MAR Nov 2016] FROM
(
SELECT CustName, OrderedProduct, QtyOrdered,
((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
FROM [ORDERS]
) src
PIVOT
(
SUM(QtyOrdered)
FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol
PIVOT
(
SUM(Margin)
FOR YearMonthMar IN ([MAR Dec 2016], [MAR Nov 2016])
) pvtmar
WHERE CustName='Bob'
我会期望看到的表是
CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
Bob | Canned Tuna | 16 | 4 | 48 | 4
我实际上看到的表是
CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
Bob | Canned Tuna | 16 | NULL | 48 | NULL
Bob | Canned Tuna | NULL | 4 | NULL | 4
因此,似乎没有应用保证金的枢纽,每个订单我都会得到一行.我尝试使用的方法实际上是否可以实现目标?或者,将交叉表查询用于VOL和MAR还是将透视表和交叉表混合使用(VOL的透视表,MAR的交叉表?)会更好? )
So, the pivot on margin does not seem to be applied, and I get a row for each order. Is what I'm trying to achieve actually possible with the method I'm trying to use, or would I be better to use a crosstab query for VOL and MAR or a mixture of pivot and crosstab (pivot for VOL, crosstab for MAR?)
任何建议,我们将不胜感激.
Any advice greatly appreciated.
推荐答案
您可以稍微简化查询
示例
Select *
From (
Select A.CustName
,A.OrderedProduct
,B.*
From ORDERS A
Cross Apply ( values ('VOL '+left(DateName(Month,OrderDate),3)+' '+DateName(Year,OrderDate), QtyOrdered )
,('MAR '+left(DateName(Month,OrderDate),3)+' '+DateName(Year,OrderDate),((UnitPrice-UnitCost)*QtyOrdered) )
) B (Item,Value)
Where CustName='Bob'
) A
Pivot (sum([Value]) For [Item] in ([VOL Dec 2016], [VOL Nov 2016],[MAR Dec 2016], [MAR Nov 2016]) ) p
返回
这篇关于SQL Server在2个(或多个)聚合上枢转的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!