sql-server - 为什么我的数据透视查询无法正确分组？

Question

我使用的是 SQL Server 2014，并且有以下Pivot 查询。我的问题是查询未正确分组。实际的 T-SQL 查询及其输出如下所示:

SELECT [Market], [Actual], [Budget]

FROM

(

select distinct Market, 
       sum(rn) over (partition by Market) as [RN], 
       sum(rn) over () as [Total],
       cast(CAST(((100.0 * (sum(rn) over (partition by Market))/(sum(rn) over ()))) AS DECIMAL(19,1)) as varchar(10))+ ' ' + '%'  as [Percentage],
       'Actual' as [Type]

from [View1]


UNION ALL

select distinct Market, 
       sum(rn) over (partition by market) as [RN], 
       sum(rn) over () as Total,
       cast(CAST(((100.0 * (sum(rn) over (partition by Market))/(sum(rn) over ()))) AS DECIMAL(19,1)) as varchar(10))+ ' ' + '%'  as [Percentage],
       'Budget' as [Type]
from [Budget1]


)xx

PIVOT

(

MIN([Percentage])

FOR [Type] IN ([Actual], [Budget])


) AS pvt_table

我的源数据的摘录(即上面的内部查询):

Market           RN     Total   Percentage    Type
Belgium          240    5337    4.5 %         Budget
Belgium          213    5191    4.1 %         Actual
Central Europe    35    5337    0.7 %         Budget
Central Europe   100    5191    1.9 %         Actual

运行整个查询的当前输出的摘录:

Market           Actual    Budget
Belgium          4.1 %     NULL
Belgium          NULL      4.5 %
Central Europe   1.9 %     NULL
Central Europe   NULL      0.7 %

我期望的结果:

Market           Actual    Budget
Belgium          4.1 %     4.5 %
Central Europe   1.9 %     0.7 %

我希望按市场分组在我的Pivot 查询中是明确的。

Answer 1

PIVOT通过将表达式中的一列(在您的情况下为 Type)中的唯一值转换为输出中的多列(Actual, Budget)来旋转表值表达式，并运行最终输出中需要的任何剩余列值所需的聚合。

因此，您必须优化内部查询，以便仅选择必要的列:

SELECT [Market], [Actual], [Budget]    
FROM 
(
   SELECT [Market], percentage, type 
   FROM  xxx -- You inner query
) AS src 
PIVOT
(
   MIN([Percentage])
   FOR [Type] IN ([Actual], [Budget])
) AS pvt_table

这意味着您必须保留 RN 和 Total 列。