怎么样把数据从一变俩？

[dejoy]

有这个一个数据
ID InDptID OutDptID Num
1 3 4 5

意思就是从部门3调了5个单位数量 到 部门4.

用什么SQL返回如下数据：
ID DptID InNum OutNum
1 3 0 5
1 4 5 0

附生成SQL：
CREATE TABLE [T] (
[ID] int,
[InDptID] int,
[OutDptID] int,
[Num] int
)
GO

--
-- Data for table T (LIMIT 0,500)
--

INSERT INTO [T] ([ID], [InDptID], [OutDptID], [Num])
VALUES
(1, 3, 4, 5)
GO

[OracleRoob]

CREATE TABLE #t (
ID int,
InDptID int,
OutDptID int,
Num int
)

insert into #t (ID, InDptID, OutDptID, Num)VALUES (1, 3, 4, 5)

insert into #t (ID, InDptID, OutDptID, Num)VALUES (2, 7, 8, 9)

select * from #t

select ID, InDptID as DptID , 0 as InNum , Num as OutNum from #t
union all
select ID, OutDptID as DptID , Num as InNum ,0 as OutNum from #t

drop table #t

[jjhlover]

select ID,InDptID,0 as InNum,Num as OutNum from T
union
select ID,OutDptID,Num,0 from T

[hellowork]

select ID ,InDptID as DptID, case when Num > 0 then 0 else Num end as InNum, case when Num>0 then Num else 0 end as OutNum
from t where Num > 0
union all
select ID ,OutDptID as DptID, case when Num > 0 then Num else 0 end as InNum, case when Num>0 then 0 else Num end as OutNum
from t where Num > 0

[hellowork]

画蛇添足了.
select ID ,InDptID as DptID, 0 as InNum, Num as OutNum
from t where Num > 0
union all
select ID ,OutDptID ,Num,0
from t where Num > 0

[十一月猪]

select id ,InDptID as dptid , 0 as inmun , num as outnum
from t where id = 1
union
select id ,OutDptID as dptid , num as inmun , 0 as outnum
from t where id = 1