需要进行查询，类似的在Excel中使用和操作的数据透视表

问题描述：

我有一个包含的SerialNo，StockItemId，类型的表，WareHouseId，NetCount 这是我的查询需要进行查询，类似的在Excel中使用和操作的数据透视表

SELECT Warehouse.WHWorkOrderDetailsSerials.SerialNo 
    ,Warehouse.WHWorkOrderDetails.StockItemId, Warehouse.WHWorkOrderHeader.Type 
    ,Warehouse.WHWorkOrderHeader.WareHouseId 
    ,CASE 
     WHEN Warehouse.WHWorkOrderHeader.Type IN ('RS', 'RR', 'RT', 'OB') 
     THEN '1' 
     ELSE '-1' 
     END AS NetCount 
FROM Warehouse.WHWorkOrderDetails 
INNER JOIN Warehouse.WHWorkOrderDetailsSerials 
     ON Warehouse.WHWorkOrderDetails.Id = Warehouse.WHWorkOrderDetailsSerials.WorkOrderDetailId 
     INNER JOIN Warehouse.WHWorkOrderHeader 
     ON Warehouse.WHWorkOrderDetails.WHWorkOrderHeaderId = Warehouse.WHWorkOrderHeader.ID

我需要做一个数据透视表，其中包含每个WorkHouseId的NetCount之和。像这个数据透视表在Excel中一样

任何想法都可以这么做吗？

*枢轴*在MySQL和SQL Server中有很大不同。请[编辑]您的标签，让我们知道哪一个与您的情况有关。 –

有两件事：1）将NetCount THEN ELSE更改为1，-1而不是'1'，' - 1'。这是两回事。 2）这听起来很像X-Y问题（http://xyproblem.info）。你想用这些钱做什么？你需要一个数据透视表还是每个仓库编号只需一笔数额？编辑：和＃3：别名表使它更容易阅读。 – Shawn

@ O.Jones好的感谢这个信息 – Believer

答

你可以改变你的查询做一个PIVOT，而不必把它放在Excel中。

SELECT SerialNo, [1], [2], [3], [4], [5] 
    FROM 

(SELECT Warehouse.WHWorkOrderDetailsSerials.SerialNo 
,Warehouse.WHWorkOrderDetails.StockItemId, Warehouse.WHWorkOrderHeader.Type 
,Warehouse.WHWorkOrderHeader.WareHouseId 
,CASE 
    WHEN Warehouse.WHWorkOrderHeader.Type IN ('RS', 'RR', 'RT', 'OB') 
    THEN '1' 
    ELSE '-1' 
    END AS NetCount 
FROM Warehouse.WHWorkOrderDetails 
INNER JOIN Warehouse.WHWorkOrderDetailsSerials 
    ON Warehouse.WHWorkOrderDetails.Id = Warehouse.WHWorkOrderDetailsSerials.WorkOrderDetailId 
    INNER JOIN Warehouse.WHWorkOrderHeader 
    ON Warehouse.WHWorkOrderDetails.WHWorkOrderHeaderId = Warehouse.WHWorkOrderHeader.ID) AS d PIVOT(SUM(NetCount) FOR WareHouseId IN ([1],[2],[3],[4],[5]))

似乎并不需要您在原始查询中拥有的所有字段。我更新了下面的查询，只显示您需要的字段。

SELECT SerialNo, [1], [2], [3], [4], [5] 
    FROM 

(SELECT Warehouse.WHWorkOrderDetailsSerials.SerialNo 
,Warehouse.WHWorkOrderHeader.WareHouseId 
,CASE 
    WHEN Warehouse.WHWorkOrderHeader.Type IN ('RS', 'RR', 'RT', 'OB') 
    THEN '1' 
    ELSE '-1' 
    END AS NetCount 

FROM Warehouse.WHWorkOrderDetails 
INNER JOIN Warehouse.WHWorkOrderDetailsSerials 
    ON Warehouse.WHWorkOrderDetails.Id = Warehouse.WHWorkOrderDetailsSerials.WorkOrderDetailId 
    INNER JOIN Warehouse.WHWorkOrderHeader 
    ON Warehouse.WHWorkOrderDetails.WHWorkOrderHeaderId = Warehouse.WHWorkOrderHeader.ID) AS d PIVOT(SUM(NetCount) FOR WareHouseId IN ([1],[2],[3],[4],[5]))

没问题，随时评价答案。 :) –

需要进行查询，类似的在Excel中使用和操作的数据透视表

相关推荐