如何使用SQL Server创建两个具有相同列名的透视列？

Question

我想使用具有相同列的聚合函数制作两个透视列。

ItemLookupCode StoreID DepartmentID Weeks  QtySold AsOfWeekOnHand 
---------------------------------------------------------------------------------- 
610759C2000  1001  23    30    0   1.5 
610759C2000  1001  23    31    0   0 
610759C2000  1004  23    30    0   2 
610759C2000  1004  23    31    0   3.5 
610759C2000  1201  23    30   0.6395  1 
610759C2000  1201  23    31   0.6395  2 

我试着用下面的查询。但这是错误的。什么是正确的方法？

select itemlookupcode, storeid, departmentid,[30],[31] from 
(
    select 
     fr.itemlookupcode, 
     fr.storeid, 
     fr.departmentid, 
     fr.asofweekonhand, 
     fr.weeks, 
     fr.QtySold 
    from 
     #finalresult fr 
) x 
pivot 
(
    sum(QtySold) 
    for weeks in ([30],[31]) 
) p1 
pivot 
(
    sum(asofweekonhand) 
    for weeks in ([30],[31]) 
) p2 

注意

我们可以指定列名作为

Week30Sold Week31Sold Week30AsOfWeekOnHand Week31AsOfWeekOnHand 
------------------------------------------------------------------------- 

Answer 1

我想，这大概做你想要什么：

declare @t table (ItemLookupCode varchar(20), StoreID int, DepartmentID int, Weeks int, 
        QtySold decimal(10,4), AsOfWeekOnHand decimal(10,4)) 
insert into @t(ItemLookupCode,StoreID,DepartmentID,Weeks,QtySold,AsOfWeekOnHand) values 
('610759C2000',1001,23,30, 0 ,1.5), 
('610759C2000',1001,23,31, 0 , 0 ), 
('610759C2000',1004,23,30, 0 , 2 ), 
('610759C2000',1004,23,31, 0 ,3.5), 
('610759C2000',1201,23,30,0.6395, 1 ), 
('610759C2000',1201,23,31,0.6395, 2 ) 

select 
    * 
from 
    (select ItemLookupCode,StoreID,DepartmentID, 
     CONVERT(varchar(13),Weeks) + 'Qty' as Weeks, 
     QtySold from @t) t1 
    pivot (SUM(QtySold) for Weeks in ([30Qty],[31Qty])) p1 
    cross apply 
    (select CONVERT(varchar(13),Weeks) + 'AsOf' as Weeks,AsOfWeekOnHand 
    from @t t2 
    where t2.ItemLookupCode = p1.ItemLookupCode and 
    t2.DepartmentID = p1.DepartmentID and 
    t2.StoreID = p1.StoreID) t2 
    pivot (SUM(AsOfWeekOnHand) for Weeks in ([30AsOf],[31AsOf])) p2 

结果：

ItemLookupCode  StoreID  DepartmentID 30Qty  31Qty 30AsOf 31AsOf 
-------------------- ----------- ------------ ---------- -------- -------- ------- 
610759C2000   1001  23   0.0000  0.0000 1.5000 0.0000 
610759C2000   1004  23   0.0000  0.0000 2.0000 3.5000 
610759C2000   1201  23   0.6395  0.6395 1.0000 2.0000 

值得注意的是：

• 使用相同的列（S）两次不能转动 - 转动后，在枢轴条款的第一部分中提到的列不复存在，已被括号内的新列名取代。

• 我们要做的apply，而不是一个JOIN一个子查询，以避免引入复制列

• I（例如ItemLookupCode将在结果集中，如果t2是join到子查询中出现两次）借此机会在子查询

• 当我们使用APPLY我们必须使用p1作为外部引用重命名Weeks列 - 一个PIVOT会生成一个全新的结果集，用于替换任何现有的结果集/别名。

• 正如my answer to your earlier question提到的，PIVOT有效GROUP BY S中PIVOT条款中未提及的所有列 - 那么，为什么不在第一PIVOT第二转动过程中产生关注的列？因为我们已经知道每个组合ItemLookupCode,StoreID和DepartmentID本身都是唯一的，由于第一个PIVOT。