sql - 在我的情况下，如何在 SQL Server 中将行设为列？

Question

我有 2 张桌子

过去的产品

productID   deptID    year   month    price
-------------------------------------------
1           10        2015   1        45
1           10        2015   2        65


2           11        2015   1        45
2           11        2015   2        65
2           11        2015   3        44

产品当前

productID   deptID    year   month    price
-------------------------------------------
1           10        2016   1        12
1           10        2016   2        46
1           10        2016   3        77

2           11        2016   1        88

预期产出

productID   deptID    Month    PrevYear   PrevPrice    CurrYear    CurrPrice
-----------------------------------------------------------------------------------------
1           10          1       2015        45          2016            12
1           10          2       2015        65          2016            46
1           10          3       2015        0           2016            77

2           11          1       2015        45           2016            88
2           11          1       2015        65           2016            0
2           11          1       2015        44           2016            0

我试图在我的存储过程中制作如下的 unionall 和 group

SELECT ProductID,DeptID,month
into #rec
FROM (
    SELECT ProductID,DeptID,year,month FROM ProductPast
    UNION ALL
    SELECT ProductID,DeptID,year,month FROM ProductCurrent
    )
group by ProductID,DeptID,month


SELECT ProductID,DeptID,month,p.year as PrevYear, c.year as CurrYear, p.price as prevprice,c.price as currprice
FROM rec
LEFT JOIN ProductPast p on p.productid = rec.productID and p.month = rec.month
LEFT JOIN ProductCurrent c on c.productid = rec.productID and c.month = rec.month

但我没有得到确切的结果。

标签： sqlsql-serverstored-procedures

Answer 1

实际上，您在这里需要一个完整的外部连接：

SELECT
    COALESCE(pp.productID, pc.productID) AS productID,
    COALESCE(pp.deptID, pc.deptID) AS deptID,
    COALESCE(pp.month, pc.month) AS Month,
    COALESCE(pp.year, 2015) AS PrevYear,
    COALESCE(pp.price, 0) AS PrevPrice,
    COALESCE(pc.year, 2016) AS CurrYear,        
    COALESCE(pc.price, 0) AS CurrPrice
FROM ProductPast pp
FULL OUTER JOIN ProductCurrent pc
    ON pp.productID = pc.productID AND
       pp.deptID = pc.deptID AND
       pp.year = pc.year - 1 AND
       pp.month = pc.month
ORDER BY
    COALESCE(pp.productID, pc.productID),
    COALESCE(pp.deptID, pc.deptID),
    COALESCE(pp.month, pc.month);

演示

请注意，另一种方法是使用包含所有年份和月份（可能还包括产品和部门）的日历表。然后，您可以执行一系列常规的内/左连接来获得您想要的结果。