How to make rows into columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make rows into columns

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-14 : 02:12:04

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-02-14 : 02:46:29

Select
Itemid,
Datephysical,
sum(Case when Statusissue = 1 then Qty else 0 end) as ReceivedQty,
sum(Case When Statusissue = 0 then Qty else 0 end) as TransferQty
From Table1
Where datephysical >='20120214' and datephysical < '20120215'
group by Itemid,
Datephysical

Madhivanan

Failing to plan is Planning to fail

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-02-14 : 04:27:30

Thank you madhivanan.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources