转载一个过程供参考:
create proc up_transpose_table (@tablename nvarchar(256))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('too many rows, could not be transposed!',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
end
create proc up_transpose_table (@tablename nvarchar(256))
as
begin
set nocount on
declare @col nvarchar(256)
declare @makesql nvarchar(4000)
declare @insertsql nvarchar(4000)
declare @caculatesql nvarchar(400)
declare @count int
declare @i int
create table #tmp (colname nvarchar(20))
select @caculatesql = 'select @count=count(1) from ' + @tablename
exec sp_executesql @caculatesql, N'@count int output',@count output
if @count >=1024
begin
raiserror('too many rows, could not be transposed!',16,1)
end
else
begin
select @i=0
while @count >0
begin
select @i=@i+1
select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
exec(@makesql)
select @count=@count-1
end
declare my_cursor cursor for
select name from syscolumns where id=object_id(@tablename) order by colid
open my_cursor
fetch next from my_cursor into @col
while @@fetch_status = 0
begin
select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
select @insertsql =N'insert #tmp values ('''+@col+ ''','
execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
end
end
from (select distinct top 100 percent 纸度 from 表 order by 纸度) aselect @sql=@sql+' from (select distinct top 100 percent 纸号 from 表 order by 纸号) b '
print @sqlexec(@sql)
insert into #ttt values('A01' , '33' , 1000 )
insert into #ttt values('A02' , '29' , 800 )
insert into #ttt values('A01' , '31' , 700 )
declare @sql varchar(8000)
set @sql='select 纸号 '
select @sql=@sql+', sum(case when 纸度= '''+ 纸度+''' then 重量 else 0 end) as '''+纸度+''''
from (select distinct 纸度 from #ttt) as a
select @sql=@sql+' from #ttt group by 纸号 '
print @sql
exec(@sql)
纸号 varchar(8) null
col29 int null
col30 int null
:
:
col100 int null
2、Insert into tab2(纸号) select distinct 纸号 from tab1 --把所有纸号插入TAB2
3、Update tab2 set col29=纸度 from tab1 where tab2.纸号=tab1.纸号
4、重复步骤3至col100(仅替换set后的列名)
5、已经成功。
drop proc proc_sel
gocreate proc proc_sel
as
declare @sql varchar(8000)
select distinct 纸度 into #tmp
set @sql = 'select 纸号,'
select @sql = @sql +' max(case 纸度 when '+ cast(纸度 as varchar) +' then 重量 else 0 end) as '''+ 纸度 +''',' from #tmp
set @sql = left(@sql, len(@sql)-1) +' from tblA group by 纸号'
--print @sql
exec(@sql)
goexec proc_sel