create table t1(store int,flowno int,name varchar(10),价格 int,日期 datetime)
insert into t1 select 321,100,'XX',1,'2011-10-20'
insert into t1 select 321,100,'XX',12,'2011-10-20'
insert into t1 select 321,101,'XX',5,'2011-10-20'
insert into t1 select 321,102,'XX',10,'2011-10-20'
insert into t1 select 321,102,'XX',50,'2011-10-20'
create table t2(store int,flowno int,总价 int,现金 int,银行卡 int,代金卷 int,日期 datetime)
insert into t2 select 321,100,13,13,0,0,'2011-10-20'
insert into t2 select 321,101,5,0,0,5,'2011-10-20'
insert into t2 select 321,102,60,0,60,0,'2011-10-20'
go
select a.store,a.flowno,a.name,a.价格,
(case when b.现金>0 then a.价格 else 0 end)现金,
(case when b.银行卡>0 then a.价格 else 0 end)银行卡,
(case when b.代金卷>0 then a.价格 else 0 end)代金卷,
a.日期
from t1 a inner join t2 b on a.store=b.store and a.flowno=b.flowno
/*
store       flowno      name       价格          现金          银行卡         代金卷         日期
----------- ----------- ---------- ----------- ----------- ----------- ----------- -----------------------
321         100         XX         1           1           0           0           2011-10-20 00:00:00.000
321         100         XX         12          12          0           0           2011-10-20 00:00:00.000
321         101         XX         5           0           0           5           2011-10-20 00:00:00.000
321         102         XX         10          0           10          0           2011-10-20 00:00:00.000
321         102         XX         50          0           50          0           2011-10-20 00:00:00.000

(5 行受影响)
*/
go
drop table t1,t2