sql-server - 需要使用枢轴的SQL查询的一种解决方案

select PurchaseOrderID
     , [244,231] as FirstEmp
     , [266,274] as secondEmp
     , [231,241] as ThirdEmp
  from ( select PurchaseOrderID, EmployeeID, TaxAmt
           from Purchasing.PurchaseOrderHeader
          Where EmployeeID IN (244, 231, 266, 274, 241)
                ) SourceTable
 PIVOT (
   SUM(TaxAmt)
   FOR EmployeeID IN([244,231],[266,274],[231,241])
   ) AS PVT

从上面的查询中，我必须使用数据透视表逐组检索特定GLcode的数据。

我需要类似以下的结果，这意味着一组SUM(TaxAmt)的GLCode IN (244,231)，其他第二组GLCode IN (266,274)的第三种是GLCode IN (231,241)。

---------------------------------------------------------
PurchaseOrderID     [244,231]    [266,274]    [231,241]
---------------------------------------------------------
1                    5678          10456        45643
2                    3456          5643          564
3                    34567         5678          4243
4                    5897          65645         7567
---------------------------------------------------------------

最佳答案

select PurchaseOrderID,
       sum(case when EmployeeID in (244, 231) then TaxAmt end) as "244,231",
       sum(case when EmployeeID in (266, 274) then TaxAmt end) as "266,274",
       sum(case when EmployeeID in (231, 241) then TaxAmt end) as "231,241"
from PurchaseOrderHeader
where EmployeeID in(244, 231, 266, 274, 241)
group by PurchaseOrderID

枢轴版本:

select PurchaseOrderID,
       [244]+[231] as "244,231",
       [266]+[274] as "266,274",
       [231]+[241] as "231,241"
from
  (
    select EmployeeID, TaxAmt, PurchaseOrderID
    from PurchaseOrderHeader
    where EmployeeID in(244, 231, 266, 274, 241)
  ) as P1
pivot
  (
    sum(TaxAmt) for EmployeeID in ([244],[231],[266],[274],[241])
  ) as P2

关于sql-server - 需要使用枢轴的SQL查询的一种解决方案，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/10055196/