列自动生成（交叉表/crosstab）

if not object_id('today') is null
drop table today
Go
create table today(id int identity(1,1) primary key,inmoney int,date smalldatetime,name varchar(20))
 
insert into today
select 500,'2008-5-1','厂家1'
union all select 500,'2008-6-1','厂家2'
union all select 500,'2008-7-1','厂家1'
union all select 500,'2008-5-2','厂家3'
union all select 500,'2008-7-2','厂家2'
union all select 500,'2008-8-1','厂家1'
union all select 500,'2008-6-1','厂家1'

 

declare @col as varchar(4000),@sql varchar(8000)
set @col = ''

 

drop table #temp

select distinct [name] into #temp from today

 

select @col = @col + '[' + [name] + ']=sum(case when [name]='''
 + [name] + ''' then [inmoney] else 0 end),'
from #temp

 

--方法1：通过case when语法进行crosstab逐项聚合查询
set @sql = 'select left(convert(varchar,date,20),7) 月份,' + left(@col,len(@col)-1)
set @sql = @sql + ' from today group by left(convert(varchar,date,20),7)'

execute (@sql)

 

--方法2（sql server 2005以上）：通过pivot来实现
select @col = ''
select @col = @col + 'isnull(sum([' + [name] + ']),0) ' + [name] + ',' from #temp

 

set @sql = 'select left(convert(varchar,a.date,20),7) 月份,'
 + left(@col,len(@col)-1) + ' from today pivot(sum(inmoney) for name in('

 

select @col = ''
select @col = @col + '[' + [name] + '],' from #temp

set @sql = @sql + left(@col,len(@col)-1) + ')) as a group by left(convert(varchar,a.date,20),7)'

execute (@sql)