表A:
Num protype
1 80
2 21
3 110
4 23
======================
表B:
IP protype
192.168.1.1 80
192.168.1.1 110
192.168.1.1 23
192.168.1.2 110
192.168.1.2 80
192.168.1.3 21
==============================
显示结果:
IP 80 21 110 23
192.168.1.1 Y N Y Y
192.168.1.2 Y N Y N
192.168.1.3 N Y N N
Num protype
1 80
2 21
3 110
4 23
======================
表B:
IP protype
192.168.1.1 80
192.168.1.1 110
192.168.1.1 23
192.168.1.2 110
192.168.1.2 80
192.168.1.3 21
==============================
显示结果:
IP 80 21 110 23
192.168.1.1 Y N Y Y
192.168.1.2 Y N Y N
192.168.1.3 N Y N N
insert into tba select 1,80
union all select 2,21
union all select 3,110
union all select 4,23create table tbb(ip varchar(100),protype int)
insert into tbb select '192.168.1.1', 80union all select '192.168.1.1', 110
union all select '192.168.1.1', 23
union all select '192.168.1.2', 110
union all select '192.168.1.2', 80
union all select '192.168.1.3', 21declare @s varchar(8000)
set @s=''
select @s=@s+','+'max(case protype when '''+ltrim(protype)+''' then ''Y'' else ''N'' end )'''+quotename(protype)+'''' from tba
set @s='select ip '+ @s +' from tbb group by ip'
exec (@s)
from aset @sql = 'select ip,' + left(@sql,len(@sql) - 1) +' from b group by ip'exec(@sql)