这样有意义吗?难道一个月的数据你的报表要30列?时间问题,只提供思路
declare @col varchar(600)
select @col = ' '
select @col = @col + ', ' + distinct field2 + ' int ' from tab1
--select distinct field1, field2 into #t1
exec ( 'create table tab2 ( field1 char(2) ' + @col + ' ) ')if object_id('tempdb.dbo.#t2') is null
create table #t2 ( field1 char(2), field2 int )
else
truncate table #t2select @rowcount = count(distinct field1,field2) from tab1
select @i = 0 , @field1 = '', @field2 = '', @field3 = 0
if @i < @rowcount
begin
select @field1 = field1, @field2 = field2, @field3 = field3 from tab1 where trim(field2)+trim(field1) > trim(@field2)+trim(@field1) order by field2, field1
exec( 'insert into #t2 (field1, '+ @field2 + ' vules ( @field ........ //可以从SYSCOLUMNS中取判断
exec('alter table #t2 add ' + @field2 + ' int ')
select @i = @i + 1
end
统计……
写入tab2
declare @col varchar(600)
select @col = ' '
select @col = @col + ', ' + distinct field2 + ' int ' from tab1
--select distinct field1, field2 into #t1
exec ( 'create table tab2 ( field1 char(2) ' + @col + ' ) ')if object_id('tempdb.dbo.#t2') is null
create table #t2 ( field1 char(2), field2 int )
else
truncate table #t2select @rowcount = count(distinct field1,field2) from tab1
select @i = 0 , @field1 = '', @field2 = '', @field3 = 0
if @i < @rowcount
begin
select @field1 = field1, @field2 = field2, @field3 = field3 from tab1 where trim(field2)+trim(field1) > trim(@field2)+trim(@field1) order by field2, field1
exec( 'insert into #t2 (field1, '+ @field2 + ' vules ( @field ........ //可以从SYSCOLUMNS中取判断
exec('alter table #t2 add ' + @field2 + ' int ')
select @i = @i + 1
end
统计……
写入tab2
这个问题比较麻烦。
更何况你的横向字段列表是动态的。要做出一个通用的程序,比较困难,但也不是不可以。给你提供个思路,有兴趣可以自己试一下:
首先是应该在存储过程里实现。
field1 field2 field3
A 2002-02-01 12
B 2002-02-01 13
C 2002-02-03 23distinct 上表中的field2字段用游标取出,然后创建动态临时表,创建表时,取游标,将各日期一个一个第取出,并创建上去即可。
这样交叉表就形成了。至于数据如何插入,自己用SQL语句写一下。
declare @sql varchar(3000)declare cr CURSOR for select field2 from Table1 group by field2 order by field2
OPEN crset @sql=''
FETCH NEXT FROM cr INTO @dtWHILE @@FETCH_STATUS = 0
BEGIN
set @sql='(case when field2=@dt then field2 end) as '+convert(char(10),field2,20)+','+
'(select sum(field3) from table1 where field2=@dt) as '+convert(char(10),field2,20)+','
FETCH NEXT FROM cr INTO @dt
END
CLOSE cr
DEALLOCATE cr
exec('select distinct field1,'+left(@sql,len(@sql)-1)+' into #t1 from table1')
select a.*,b.hej as 合计
from #t1 a left join (select field1,sum(field3) as hej from table1 group by field1) b on a.field1=b.field1
declare @sql varchar(3000)declare cr CURSOR for select field2 from Table1 group by field2 order by field2
OPEN crset @sql=''
FETCH NEXT FROM cr INTO @dtWHILE @@FETCH_STATUS = 0
BEGIN
set @sql='(case when field2=@dt then field2 end) as '+convert(char(10),field2,20)+','+
'(select sum(field3) from table1 where field2=@dt) as '+convert(char(10),field2,20)+','
FETCH NEXT FROM cr INTO @dt
END
CLOSE cr
DEALLOCATE cr
exec('select distinct field1,'+left(@sql,len(@sql)-1)+' into #t1 from table1')
select a.*,b.hej as 合计
from #t1 a left join (select field1,sum(field3) as hej from table1 group by field1) b on a.field1=b.field1
ERROR MESSAGE:
服务器: 消息 156,级别 15,状态 1,行 18
在关键字 'left' 附近有语法错误。
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
declare @field2 varchar(20)
declare get_f2 cursor for
select distinct field2 from tablename
open get_f2
fetch next from get_f2 into @field2
--print @@fetch_status
set @sql1=''
set @sql2=''
while (@@FETCH_STATUS<>-1)
begin
set @sql1=@sql1+'sum((case when field2='''+@field2+''' then field3 else 0 end)) as '''+@field2+''','
set @sql2=@sql2+'sum(a.['+@field2+']),'
fetch next from get_f2 into @field2
end--print @sql1
set @sql='select field1,'+@sql1+'sum(field3) as ''合计'' from tablename group by field1,field2 '
set @sql=@sql+'union select ''合计'','+@sql2+'sum(a.[合计]) from (' +@sql+') as a order by field1'exec(@sql)close get_f2
deallocate get_f2
declare @sql varchar(3000),@sql2 varchar(3000),@f2 varchar(20)declare cr CURSOR for select field2 from tb1 group by field2 order by field2
OPEN crset @sql=''
set @sql2=''
FETCH NEXT FROM cr INTO @dtWHILE @@FETCH_STATUS = 0
BEGIN
set @f2=''''+convert(char(10),@dt,126)+''''
set @sql=@sql+'sum(case when field2='+@f2+' then field3 else 0 end) as '+@f2+','
set @sql2=@sql2+'(select sum(field3) from tb1 where field2='+@f2+') as '+@f2+','
FETCH NEXT FROM cr INTO @dt
END
CLOSE cr
DEALLOCATE cr
set @sql='select distinct field1,'+@sql+'sum(field3) as ''合计'' from tb1 group by field1,field2 '
set @sql2=@sql+' union select ''合计'','+@sql2+'sum(a.合计) from (' +@sql+') as a order by field1'
exec(@sql2)
go
declare @sql varchar(3000),@sql2 varchar(3000),@f2 varchar(20)declare cr CURSOR for select field2 from tb1 group by field2 order by field2
OPEN crset @sql=''
set @sql2=''
FETCH NEXT FROM cr INTO @dtWHILE @@FETCH_STATUS = 0
BEGIN
set @f2=''''+convert(char(10),@dt,126)+''''
set @sql=@sql+'sum(case when field2='+@f2+' then field3 else 0 end) as '+@f2+','
set @sql2=@sql2+'(select sum(field3) from tb1 where field2='+@f2+') as '+@f2+','
FETCH NEXT FROM cr INTO @dt
END
CLOSE cr
DEALLOCATE cr
set @sql='select distinct field1,'+@sql+'sum(field3) as ''合计'' from tb1 group by field1,field2 '
set @sql2=@sql+' union select ''合计'','+@sql2+'sum(a.合计) from (' +@sql+') as a order by field1'
exec(@sql2)
go
应改为: group by field1
原表记录:
field1 field2 field3
A 2002-06-01 00:00:00 234
C 2002-06-01 00:00:00 676
A 2002-06-01 00:00:00 4654
B 2002-09-01 00:00:00 34(所影响的行数为 4 行)
结果(OpenVMS(半知半解)写的)field1 2002-06-01 2002-09-01 合计
---------- ----------- ----------- -----------
A 4888 0 4888
B 0 34 34
C 676 0 676
合计 5564 34 5598