Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

need one solution for sql query using pivot

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
---------------------------------------------------------------
like image 240
Vimal bhatt Avatar asked Apr 07 '12 14:04

Vimal bhatt


1 Answers

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
like image 118
Mikael Eriksson Avatar answered Sep 23 '22 02:09

Mikael Eriksson