Rows into Column

[jovillanuev]

Hi Forumer's

kindly please optimize my query. 
I need to separate the colums for statusissue = 0 and statusissue =1
my query create double entry.


]--Sample Table
Create Table #Table1
(Itemid nvarchar(10),
Datephysical datetime,
Statusissue int,
Qty numeric(28,12)) 

--Sanple Data
Insert into #table1 (Itemid, Datephysical, Statusissue, Qty) values ('HTC1175','2012-02-14',1,50)
Insert into #table1 (Itemid, Datephysical, Statusissue, Qty) values ('HTC1175','2012-02-14',0,-50)
Insert into #table1 (Itemid, Datephysical, Statusissue, Qty) values ('HTC1175-IR','2012-02-14',1,380)
Insert into #table1 (Itemid, Datephysical, Statusissue, Qty) values ('HTC1175-IR','2012-02-14',1,-380)

--expected Result:
Itemid|Datephysical|ReceivedQty|TransferQty
-------------------------------------------
HTC1175|2012-02-14|50|-50
HTC1175-IR|2012-02-14|380|-380

--My Query
Select
Itemid,
Datephysical,
Case when Statusissue = 1 then Qty else 0 end as ReceivedQty,
Case When Statusissue = 0 then Qty else 0 end as TransferQty
From Table1
Where datephysical >='2012-02-14' and datephysical < '2012-02-15' 

--Derived result
The result of my Querynis like this:
Itemid|Datephysical|ReceivedQty|TransferQty
-------------------------------------------
HTC1175|2012-02-14|0 |-50
HTC1175|2012-02-14|50| 0
HTC1175-IR|2012-02-14| 0 |-380
HTC1175-IR|2012-02-14|380| 0

Thank you in advance..
JOV

[Tess James]

Try this query.

Select
Itemid,
Datephysical,
isnull(max(Case when Statusissue = 1 then Qty else null end),0) as ReceivedQty,
isnull(max(Case When Statusissue = 0 then Qty else null end),0) as TransferQty
From #Table1
Where datephysical >='2012-02-14' and datephysical < '2012-02-15' 
group by Itemid,Datephysical