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查询中是明确的。

标签： sql-servertsqlpivot

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

这意味着您必须离开columns RN。Total