表A
Billno Qty
C1 100
C2 125表B
Billno RoleID Flag
C1 1 1
C1 2 0
C1 3 0
C1 4 0
C2 1 0
C2 2 0
C2 3 0
C2 4 0顯示的結果為:其中Flag1即為B表的RoleID=1時的Flag值
Billno Qty Flag1 Flag2 Flag3 Flag4
C1 100 1 0 0 0
C2 125 0 0 0 0
Billno Qty
C1 100
C2 125表B
Billno RoleID Flag
C1 1 1
C1 2 0
C1 3 0
C1 4 0
C2 1 0
C2 2 0
C2 3 0
C2 4 0顯示的結果為:其中Flag1即為B表的RoleID=1時的Flag值
Billno Qty Flag1 Flag2 Flag3 Flag4
C1 100 1 0 0 0
C2 125 0 0 0 0
declare @表A table (Billno varchar(2),Qty int)
insert into @表A
select 'C1',100 union all
select 'C2',125declare @表B table (Billno varchar(2),RoleID int,Flag int)
insert into @表B
select 'C1',1,1 union all
select 'C1',2,0 union all
select 'C1',3,0 union all
select 'C1',4,0 union all
select 'C2',1,0 union all
select 'C2',2,0 union all
select 'C2',3,0 union all
select 'C2',4,0select a.Billno,a.Qty,
sum(case b.RoleID when 1 then Flag else 0 end) Flag1,
sum(case b.RoleID when 2 then Flag else 0 end) Flag2,
sum(case b.RoleID when 3 then Flag else 0 end) Flag3,
sum(case b.RoleID when 4 then Flag else 0 end) Flag4
from @表B b left join @表A a on a.Billno=b.Billno
group by a.Billno,a.Qty
/*
Billno Qty Flag1 Flag2 Flag3 Flag4
------ ----------- ----------- ----------- ----------- -----------
C1 100 1 0 0 0
C2 125 0 0 0 0
*/
a.Qty,
(case b.RoleID when 1 then Flag end) as Flag1,
(case b.RoleID when 2 then Flag end) as Flag2,
(case b.RoleID when 3 then Flag end) as Flag3,
(case b.RoleID when 4 then Flag end) as Flag4
from A a
left join B b on a.Billno =b.Billno
create table t1
(
billno varchar(2),
qty int
)
insert into t1 (billno,qty) values ('C1',100),('C2',125)
create table t2
(
billno varchar(2),
roleid int,
flag int
)
insert into t2
select 'C1', 1, 1 union all
select 'C1', 2, 0 union all
select 'C1', 3, 0 union all
select 'C1', 4, 0 union all
select 'C2', 1, 0 union all
select 'C2', 2, 0 union all
select 'C2', 3, 0 union all
select 'C2', 4, 0;with aaa as
(select billno,max(case when roleid=1 then flag end) as [flag1],
max(case when roleid=2 then flag end) as [flag2],
max(case when roleid=3 then flag end) as [flag3],
max(case when roleid=4 then flag end) as [flag4] from t2 group by billno)
select aaa.billno,t1.qty,aaa.flag1,aaa.flag2,aaa.flag3,aaa.flag4 from aaa inner join t1 on aaa.billno=t1.billno
[1] as Flag1,
[2] as Flag2,
[3] as Flag3,
[4] as Flag4
from test2
pivot
(count([ Flag])
for [Billno] in ([1],[2],[3],[4])
) pvt
--如果数据库是2005版本以上的话
a.Qty,
[1] as Flag1,
[2] as Flag2,
[3] as Flag3,
[4] as Flag4
from A a
left join B b on a.Billno =b.Billno
pivot
(count([ Flag])
for [RoleID] in ([1],[2],[3],[4])
) pvt
--如果数据库是2005版本以上的话
--再次修改
select billno,qty,
[1] as Flag1,
[2] as Flag2,
[3] as Flag3,
[4] as Flag4
from
(select a.Billno,a.roleid,a.flag,
b.qty
from t2 a
left join t1 b on a.Billno =b.Billno
) tb
pivot
(sum(flag)
for RoleID in ([1],[2],[3],[4])
) pvt
------------------------------------
billno,qty,Flag1,Flag2,Flag3,Flag4
C1,100,1,0,0,0
C2,125,0,0,0,0
insert into @表A
select 'C1',100 union all
select 'C2',125declare @表B table (Billno varchar(2),RoleID int,Flag int)
insert into @表B
select 'C1',1,1 union all
select 'C1',2,0 union all
select 'C1',3,0 union all
select 'C1',4,0 union all
select 'C2',1,0 union all
select 'C2',2,0 union all
select 'C2',3,0 union all
select 'C2',4,0select a.Billno,a.Qty,
b.Flag as Flag1,
c.Flag as Flag2,
d.Flag as Flag3,
e.Flag as Flag4
from @表A a ,@表B b ,@表B c ,@表B d ,@表B e
where a.Billno=b.Billno and b.RoleID=1
and a.Billno=c.Billno and c.RoleID=2
and a.Billno=d.Billno and d.RoleID=3
and a.Billno=e.Billno and e.RoleID=4/*
Billno Qty Flag1 Flag2 Flag3 Flag4
------ ----------- ----------- ----------- ----------- -----------
C1 100 1 0 0 0
C2 125 0 0 0 0(2 行受影响)
*/