>

SerialNo, StockItemId, Type, WareHouseId, NetCount를 포함하는 테이블이 있습니다 이건 내 질문이다

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에서

어떻게 할 생각입니까?

  • 답변 # 1

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

관련 자료

  • 이전 javascript - 객체가 Google Closure의 유형인지 확인하고 변환 하시겠습니까?
  • 다음 java - 작업 공간을 요청한 후 시작시 Eclipse 네온 오류