Abdalmo'men Elsayed Abdalmo'men Elsayed - 22 days ago 6
SQL Question

Need to make a query which like pivot table in excel with sum operation

I have a Table that contains a SerialNo,StockItemId,Type,WareHouseId,NetCount
and this is my query

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


Figure 1
I need to make a pivot table which have the sum of NetCount for every WorkHouseId Like this pivot table in excel
Figure 2

Any Idea to do that ?

Answer Source

You can change your query to do a PIVOT, without having to put it in 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]) )

It doesn't seem like you will need all of the fields that you have in the original query. I updated the below query to only show the fields that you need.

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]) )