insert into t2(f1,f2,f3,f4,f5,f6) select f1,f2,
(case when f1 like '%出仓' then '' else f3 end),
(case when f1 like '%出仓' then '' else f4 end),
(case when f1 like '%进仓' then f3 else '' end),
(case when f1 like '%进仓' then f4 else '' end)
order by f2
(case when f1 like '%出仓' then '' else f3 end),
(case when f1 like '%出仓' then '' else f4 end),
(case when f1 like '%进仓' then f3 else '' end),
(case when f1 like '%进仓' then f4 else '' end)
order by f2
f1,
f2,
(Case When f1 Like N'%进仓' Then Rtrim(f3) Else '' End) As f3,
(Case When f1 Like N'%进仓' Then Rtrim(f4) Else '' End) As f4,
(Case When f1 Like N'%出仓' Then Rtrim(f3) Else '' End) As f5,
(Case When f1 Like N'%出仓' Then Rtrim(f4) Else '' End) As f6
from t1
Order By f2
Create Table TEST1
(f1 Nvarchar(20),
f2 Varchar(10),
f3 Int,
f4 Int)Create Table TEST2
(f1 Nvarchar(20),
f2 Varchar(10),
f3 Int,
f4 Int,
f5 Int,
f6 Int)
--插入数据
Insert TEST1 Values(N'采购进仓', '05-06-08', 20, 30)
Insert TEST1 Values(N'其它出仓', '05-04-01', 30, 40)
Insert TEST1 Values(N'销售出仓', '05-03-05', 40, 50)
Insert TEST1 Values(N'采购进仓', '05-06-09', 50, 60)
Insert TEST1 Values(N'染厂进仓', '05-06-01', 60, 70)
Insert TEST1 Values(N'其它进仓', '05-05-02', 70, 80)
Insert TEST1 Values(N'销售出仓', '05-05-06', 80, 90)
--测试
Insert TEST2
Select
f1,
f2,
(Case When f1 Like N'%进仓' Then f3 Else 0 End) As f3,
(Case When f1 Like N'%进仓' Then f4 Else 0 End) As f4,
(Case When f1 Like N'%出仓' Then f3 Else 0 End) As f5,
(Case When f1 Like N'%出仓' Then f4 Else 0 End) As f6
from TEST1
Order By f2Select * from TEST2
--删除测试环境
Drop Table TEST1,TEST2
--结果
/*
f1 f2 f3 f4 f5 f6
销售出仓 05-03-05 0 0 40 50
其它出仓 05-04-01 0 0 30 40
其它进仓 05-05-02 70 80 0 0
销售出仓 05-05-06 0 0 80 90
染厂进仓 05-06-01 60 70 0 0
采购进仓 05-06-08 20 30 0 0
采购进仓 05-06-09 50 60 0 0
*/