I take it that the GLAcc's are a fixed set of numbers.
Make your query a subquery by wrapping in brackets and add field itr_glacctnbr
then write a
-
Select sum(Case when itr_glacctnbr='121101' then Amt else 0 end) as 121101,
-
sum(Case when itr_glacctnbr='121102' then Amt else 0 end) as 121102,
-
......
-
FROM
-
( SELECT itr_glacctnbr,
-
SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) as Amt from itr
-
where itr_transdate between '2007-12-01' and '2007-12-31'
-
group by itr_glacctnbr
-
having itr_glacctnbr='121101' or itr_glacctnbr='121102' ....
-
)a
-
Does that help
I am getting error message like this
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '121101'.
Here is my query
Select sum(Case when itr_glacctnbr='121101' then Amt else 0 end) as 121101,
sum(Case when itr_glacctnbr='121102' then Amt else 0 end) as 121102,
sum(Case when itr_glacctnbr='121103' then Amt else 0 end) as 121103,
sum(Case when itr_glacctnbr='122101' then Amt else 0 end) as 122101,
sum(Case when itr_glacctnbr='124101' then Amt else 0 end) as 124101,
sum(Case when itr_glacctnbr='124102' then Amt else 0 end) as 124102,
sum(Case when itr_glacctnbr='124104' then Amt else 0 end) as 124104,
sum(Case when itr_glacctnbr='124105' then Amt else 0 end) as 124105,
sum(Case when itr_glacctnbr='124107' then Amt else 0 end) as 124107,
sum(Case when itr_glacctnbr='124108' then Amt else 0 end) as 124108,
sum(Case when itr_glacctnbr='124109' then Amt else 0 end) as 124109,
sum(Case when itr_glacctnbr='124301' then Amt else 0 end) as 124301,
sum(Case when itr_glacctnbr='124302' then Amt else 0 end) as 124302,
sum(Case when itr_glacctnbr='124305' then Amt else 0 end) as 124305
FROM
( SELECT itr_glacctnbr,
SUM(dbo.ITR.ITR_TransQty * dbo.ITR.ITR_UnitCostAmt) as Amt from itr
where itr_transdate between '2007-12-01' and '2007-12-31'
group by itr_glacctnbr
Having itr_glacctnbr='121101'or
itr_glacctnbr='121102' or
itr_glacctnbr='121103' or
itr_glacctnbr='122101' or
itr_glacctnbr='124101' or
itr_glacctnbr='124102' or
itr_glacctnbr='124104' or
itr_glacctnbr='124105' or
itr_glacctnbr='124107' or
itr_glacctnbr='124108' or
itr_glacctnbr='124109' or
itr_glacctnbr='124301' or
itr_glacctnbr='124302' or
itr_glacctnbr='124305' )