受益非浅的SQL语句

Id EquipmentNo Type EspecialMark
1 ECB23232 A Mark1
2 ECB23323 B Mark2
3 ECB23545 C Mark1
4 ECB23454 C Mark3

Type A B C..............
EspecialMark
Mark1 1 1 0
Mark2 0 1 0
Mark3 0 0 1

 

create table #T(Id int,EquipmentNo varchar(10),Type char(1),EspecialMark varchar(10))
insert into #T select 1,'ECB23232','A','Mark1'
insert into #T select 2,'ECB23323','B','Mark2'
insert into #T select 3,'ECB23545','C','Mark1'
insert into #T select 4,'ECB23454','C','Mark3'
insert into #T select 5,'ECB23458','C','Mark2'
insert into #T select 5,'ECB23453','C','Mark2'
--执行动态交叉表查询
declare @s varchar(8000)
set @s = 'select Type=EspecialMark'

select @s = @s + ',['+Type+']=max(case when Type='''+Type+''' then 1 else 0 end)'
from (select distinct top 100 percent Type from #T order by Type) a
print @s
set @s = @s+' from #T group by EspecialMark order by EspecialMark'
print @s
exec(@s)

 

--输出结果
/*
Type A B C
----- --- --- ---
Mark1 1 1 0
Mark2 0 1 0
Mark3 0 0 1
*/