如何使用SQL Server中的一个列在pivot表中生成多个聚合列?

I wants to show the cost and sales by year.

我想展示一下成本和销售额。

Error msg:

错误味精:

The column name "2016" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

PIVOT操作符中指定的列名“2016”与PIVOT参数中的现有列名冲突。

The column name "2017" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

PIVOT操作符中指定的列名“2017”与PIVOT参数中的现有列名冲突。

The column '2016' was specified multiple times for 'pivSales'.

“2016”一栏被多次指定为“pivSales”。

Note

请注意

I can understand why it shows error, But I don't know the way to get an output in my scenario.

我可以理解为什么它会显示错误，但是我不知道如何在我的场景中获得输出。

1 个解决方案

#1

No need for the PIVOT. Just apply a conditional aggregation

不需要主元。只要应用一个条件聚合。

select 
    StoreID,
    Department.Name Department,
    Category.Name Category, 
    Sum(case when Year(Time)=2016 then ExtendedCost end) [Cost(2016)],
    Sum(case when Year(Time)=2017 then ExtendedCost end) [Cost(2017)],
    Sum(case when Year(Time)=2016 then ExtendedPrice end) [Sales(2016)],
    Sum(case when Year(Time)=2017 then ExtendedPrice end) [Sales(2017)],
from F_itemDailySalesParent
Inner join item with(Nolock) on item.id = F_itemDailySalesParent.ItemID
Left join Department with(Nolock) on Department.ID = item.DepartmentID
Left join Category with(Nolock) on Category.ID =item.CategoryID
where DATEPART(yyyy,Time) in (2016,2017)
group by StoreID,Department.Name,Category.Name
order by StoreID

Edit - Using your original query and applying a PIVOT

编辑——使用原始查询并应用一个PIVOT

Select *
 From (
        Select StoreID
              ,Department
              ,Category
              ,B.*
         From (
                select 
                    DATEPART(yyyy,Time) Years,
                    StoreID,
                    Department.Name Department,
                    Category.Name Category, 
                    Sum(ExtendedCost) Cost,
                    sum(ExtendedPrice) Sales
                from F_itemDailySalesParent
                Inner join item with(Nolock) on item.id = F_itemDailySalesParent.ItemID
                Left join Department with(Nolock) on Department.ID = item.DepartmentID
                Left join Category with(Nolock) on Category.ID =item.CategoryID
                where DATEPART(yyyy,Time) in (2016,2017)
                group by DATEPART(yyyy,Time),StoreID,Department.Name,Category.Name
              ) A
         Cross Apply ( values (concat('cost(',Years,')'),Cost)
                             ,(concat('sales(',Years,')'),Sales)
                     ) B (Item,Value)
      ) src
 Pivot (sum[Value]) For [Item] in ([cost(2016)],[cost(2017)],[sales(2016)],[sales(2017)] ) p