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
From above query I have to retrieve data for particular GLcode group-wise using pivot.
I have need a result like the following, which means SUM(TaxAmt)
for one group GLCode IN (244,231)
and other second For GLCode IN (266,274)
and third is 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
Pivot version:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With