交叉表动态列

create table #test(name  nvarchar(20),type  nvarchar(20),category  nvarchar(20))
--select * from #test
insert into #test(name,type,category) values ('n1','t1','c1');
insert into #test(name,type,category) values ('n1','t1','c1');
insert into #test(name,type,category) values ('n2','t2','c1');
insert into #test(name,type,category) values ('n3','t3','c3');
insert into #test(name,type,category) values ('n3','t3','c4');
insert into #test(name,type,category) values ('n4','t4','c5');
insert into #test(name,type,category) values ('n1','t1','c1');


--type固定
select category,name,sum(case when type='t1' then 1 else 0 end) as 't1',sum(case when type='t2' then 1 else 0 end) as 't2',sum(case when type='t3' then 1 else 0 end) as 't3' from #test group by name,category
--如果type不固定
--使用动态SQL语句   交叉表
Declare @SQL Varchar(8000)
Select @SQL = 'select category, name'
Select @SQL = @SQL + ', SUM(case when type= ''' + type + ''' Then 1 Else 0 End) As ' + type
from #TEST Group By type
select @SQL = @SQL + ' from #TEST group by category, name Order By category, name'
print @SQL
EXEC(@SQL)
drop table #test
GO