来自 MS 查询计算和分组问题的 Excel 2010 数据透视表

Question

第一次在这里发帖，你们都帮了我很多。

我的问题是我正在使用 ODBC 连接到 SQL Server 2008 R2 数据库在 Excel 2010 中构建销售委员会。我有两个行组和两个列组。组按父子顺序排列。

行组是产品等级和产品。列组是日期和地区(东部和西部)。值(value)是按地区划分的产品价格。我正在尝试在每个给出(东部价格 - 西部价格)的区域组之后添加一个小计。有时只报告一个区域，因此如果是西部，则不应存在小计。

我尝试了计算项目的组合，这几乎可以工作，但为每个区域添加了一列。或者在 SQL Server 中进行工作，除了 Product Grade 分组将所有产品分布在每个父组中之外几乎可以工作。出于显而易见的原因，我更喜欢在 SQL Server 中进行这项工作，而且我是在 Excel 中构建数据的新手。我确定这是我所缺少的非常简单的东西。任何帮助将非常感激。

;with t as 
(  
   select  
      info.protype,  
      info.product,  
      case when info.pricecode = 'x' then 'East' else 'West' end as Region, 
      SUM(isnull(info.price,0)) as Price,
      case when r1.product=r2.product then SUM(x.price-y.price) else 0 end as Diff,
      info.effectdate 
   from   
      (select 
          protype, product, pricecode, price, effectdate    
      from 
          prc_table     
      where 
          pricecode in ('x','y')    
          and protype = 'z') as info  
   left join 
      prc_table r1 on info.protype = r1.protype 
                   and info.product = r1.product 
                   and info.effectdate = r1.effectdate  
                   and r1.pricecode = 'x'  
   left join 
      prc_table r2 on info.protype = r2.protype 
                   and info.product = r2.product 
                   and info.effectdate = r2.effectdate  
                   and r2.pricecode = 'y'      
   where 
      info.effectdate >= DATEADD(MM, -3, GETDATE()) 
      and info.effectdate <= GETDATE()  
   group by     
      info.effectdate, info.protype, info.product,
      r1.product, r2.product, info.pricecode, r1.price, r2.price
)          
select   
   c.codedesc as [Grade],   
   r.product as [Product],  
   r.Region as [Region],  
   r.Price as [Price],
   r.Diff as [E-W],
   r.effectdate as [Date]
from 
   t r 
inner join 
   pro_item i on r.protype = i.protype and r.product = i.product   
inner join 
   pro_duct p on i.protype = p.protype and i.product = p.product and i.1 = p.1
(product grade join)
inner join 
   xxx_codes c on p.desc3 = c.code and c.prefix = 'xxx'    
where   
    i.protype = 'z'    
    and i.loc = 'loc'    
    and p.desc4 = ''    
    and i.branch = 'm'  
order by 
    r.effectdate desc, codedesc, product

Answer 1

我明白你想要做什么。而且您在计算 sql 中的差异方面处于正确的轨道上，因为不可能根据它是东/西还是仅东来显示/隐藏数据透视表中的小计。所以用sql做是好的。我认为您在查询中存在一些设计缺陷的地方是试图将差异放在单独的列中。

问题是，如何在数据透视表中呈现它？如果您将 Price 和 Diff 作为数据透视表中的值字段，它将完全破坏布局。您只需要一个值字段 - 价格。并且您希望 East、West 和 Diff 作为您的列标题，因此它们都应该是您数据中的区域。

因此，在您的 sql 中，像您一样获取等级、产品、地区、价格和日期，将其放入临时表中，然后将差异记录插入到该临时表中，其中 region='Diff' 和 Price=实际差异值，其中该产品和生效日期同时存在东部和西部记录。

您的输出应如下所示:

Grade   Product Region  Price   Effdt                               
A       P1      East    1.5     31-Oct-14                                   
A       P2      East    3       31-Oct-14                                   
B       P3      East    5       31-Oct-14                                   
B       P4      East    2.44    31-Oct-14                                   
C       P5      East    3.67    31-Oct-14                                   
C       P6      East    10.3    31-Oct-14                                   
B       P3      West    5.5     31-Oct-14                                   
B       P4      West    2.7     31-Oct-14                                   
C       P5      West    3.8     31-Oct-14                                   
C       P6      West    10.7    31-Oct-14                                   
A       P1      East    1.5     30-Nov-14                                   
A       P2      East    3       30-Nov-14                                   
B       P3      East    5       30-Nov-14                                   
B       P4      East    2.44    30-Nov-14                                   
C       P5      East    3.67    30-Nov-14                                   
C       P6      East    10.3    30-Nov-14                                   
B       P3      Diff    -0.5    31-Oct-14                                   
B       P4      Diff    -0.26   31-Oct-14                                   
C       P5      Diff    -0.13   31-Oct-14                                   
C       P6      Diff    -0.4    31-Oct-14   

您只在适用的情况下插入差异行，因此当您对这些数据进行透视时，它将显示东+西+差异或仅显示东而不是东+差异。

看起来你很喜欢用 sql 来编写上面的查询，所以你应该能够实现它。如果您在使用它时遇到问题，请在您到达的地方发布，我会看看。

干杯