时间戳

首页 > 解决方案 > 透视表并将结果与​​列的聚合相结合

sql - 透视表并将结果与​​列的聚合相结合

问题描述

我有两个包含列的表：Sales_Margin: Branch, Item Code, Item Name, Sale Qty, Sale Rate, Cost, Margin

目录：垂直，项目 ID，零件描述

我需要以这样的方式输出，即目录表中的 VERTICAL 中的不同值是列，而销售报告表中的分支是行。我使用枢轴得到了那个部分：

我的代码：

select branch as Branch, [Vertical1], [Vertical2], [Vertical3], 
[Vertical4]
from 
( 
select branch, vertical, round((sum(margin)/sum([Sale Rate])),3) [Avg GM]
from
Sales_Margin JOIN Catalogue
ON Sales_Margin.[Item Code] = Catalogue.[ITEM ID]
group by branch, VERTICAL
) x
pivot
(
    sum([Avg GM]) 
        for vertical 
    in([Vertical1], [Vertical2], [Vertical3], [Vertical4])
) as pivot_table

但我还需要销售报告表中“保证金”列的总和，以便保证金按分支分组（将输出复制到 excel 并获取百分比）预期输出：

  Branch Vertical1 Vertical2 Vertical3  Vertical4       Margin
       A1       0%       9%       52%         0%       A1 Margin
       A2       40%      9%       66%         1%       A2 Margin    
       A3       32%      4%       57%         2%       A3 Margin    
       A4       17%      9%       65%         17%      A4 Margin

我尝试使用如下相关的子查询，但它不起作用：

select branch as Branch, [Vertical1], [Vertical2], [Vertical3], 
[Vertical4], 
(select sum(Margin) from Sales_Margin a where a.Branch = 
x.branch)
from 
(
select branch as Branch, [Vertical1], [Vertical2], [Vertical3], 
[Vertical4]
from 
( 
select branch, vertical, round((sum(margin)/sum([Sale Rate])),3) [Avg GM]
from
Sales_Margin JOIN Catalogue
ON Sales_Margin.[Item Code] = Catalogue.[ITEM ID]
group by branch, VERTICAL
) x
pivot
(
    sum([Avg GM]) 
    for vertical 
    in([Vertical1], [Vertical2], [Vertical3], [Vertical4])
) as pivot_table
) z

它说，

The multi-part identifier "x.branch" could not be bound.

有人可以让我知道如何获取按分支分组的添加数值的边距列

标签： sqlsql-server

解决方案

您需要使用z.branch，因为它引用了该外部子查询。

推荐阅读