如何在我的情况下在SQL服务器中制作数据透视表?

Question

我想按同一列显示记录。我也不知道如何描述这个问题。

我有一张桌子叫SoldQtyTable

ItemNo    Weeks    Years    QtySold    AsOfWeekOnHand
----------------------------------------------------
1           1        2017      5         3
2           1        2017      2         5
3           1        2017      66        70
1           2        2017      4         33

我想像下面这样显示

ItemNo    Years    [1QtySold]    [1_OnHand]    [2QtySold]    [2_OnHand]
-----------------------------------------------------------------------
1          2017       5               3            4            33
2          2017       2               5
3          2017      66               70

我用这种方式尝试过。但它不起作用

select 
PVT1.ItemID,
PVT1.StoreID,
PVT1.Years,
isnull([1],0) as [1QtySold], isnull([2],0) as [2QtySold],
isnull([1_OnHand],0) as [1_OnHand], isnull([2_OnHand],0) as [2_OnHand]
from 
(
SELECT
ItemID,
StoreID,
Years,
Weeks,
AsOfWeekOnHand
FROM
SoldQtyTable
) L
PIVOT
(
SUM(AsOfWeekOnHand) 
FOR Weeks IN ( [1_OnHand], [2_OnHand])
) AS PVT1
LEFT JOIN
(
SELECT
ItemID,
StoreID,
Years,
Weeks,
QtySold
FROM
SoldQtyTable
) L
PIVOT
(
SUM(QtySold) 
FOR Weeks IN ( [1soldQty], [2soldQty]
) AS PVT2 on PVT2.ItemID = PVT1.ItemID and PVT1.Years = PVT2.Years
where 
PVT1.years = 2017

Answer 1

我发现条件聚合要简单得多：

SELECT ItemID, Years,
SUM(CASE WHEN weeks = 1 THEN QtySold END) as QtySold_1,          
SUM(CASE WHEN weeks = 1 THEN AsOfWeekOnHand END) as AsOfWeekOnHand_1,
SUM(CASE WHEN weeks = 2 THEN QtySold END) as QtySold_2,          
SUM(CASE WHEN weeks = 3 THEN AsOfWeekOnHand END) as AsOfWeekOnHand_2
FROM SoldQtyTable
GROUP BY ItemID, Years
ORDER BY ItemID, Years;

Answer 2

如果要对多个列进行透视，可以通过先执行 UNPIVOT，然后仅对一个值列执行 PIVOT 来实现，如本答案中所述。

SELECT ItemID,
StoreID,
Years,
[1_QtySold],
[1_AsOfWeekOnHand] AS [1_OnHand],  
[2_QtySold],
[2_AsOfWeekOnHand] AS [2_OnHand]
FROM
(           
SELECT
ItemID,
StoreID,
Years,
Weeks + '_' + col AS col,
[value]
FROM
(
SELECT
ItemID,
StoreID,
Years,
CAST(Weeks as varchar) Weeks,
AsOfWeekOnHand,
QtySold
FROM
SoldQtyTable
WHERE Years = 2017 -- your original filter PVT1.years = 2017
) src
UNPIVOT 
(
VALUE
FOR col in (AsOfWeekOnHand, QtySold)
) unpiv
) s
PIVOT
(
SUM([value])
FOR col IN ([1_AsOfWeekOnHand], [1_QtySold], [2_AsOfWeekOnHand], [2_QtySold])
) unpiv
ORDER BY StoreID

这是SQL小提琴。