if exists (select * from sysobjects where id = object_id('proc_sky_blue') and xtype ='P') drop proc proc_sky_blue go create proc proc_sky_blue (@tablename varchar(200)) 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('表的行数太多了,我转不了',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 endgo ----------------分析 declare @tablename varchar(200) set @tablename='table1' 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('表的行数太多了,我转不了',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) +')' select @insertsql --exec(@insertsql) fetch next from my_cursor into @col end close my_cursor deallocate my_cursor select * from #tmp set nocount off drop table #tmp end end
Create table test (name char(10),km char(10),cj int) go insert test values('张三','语文',80) insert test values('张三','数学',86) insert test values('张三','英语',75)declare @sql varchar(8000) set @sql = 'select name as 姓名' select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']' from (select distinct km from test) as a select @sql = @sql+' from test group by name' exec(@sql)drop table test大概这个比较适合,运行结果: 姓名 数学 英语 语文 张三 86 75 80 我要的是这样的结果: 数学 英语 语文 86 75 80 因为我每次只取同一姓名的。我想问的是: sum(case km when '''+km+''' then cj end) ['+km+']' 我不太明白它的意思,为什么是 '''+km+''' 而不是'+km+'呢?
Create table test (name varchar(10),km varchar(10),cj int) go insert into test select'张三','语文',80 union select '张三','数学',86 union select '张三','英语',75declare @sql varchar(8000) set @sql = 'select name' select @sql = @sql + ',sum(case km when '''+ km+''' then cj else 0 end)['+km+']' from (select distinct km from test3) as a select @sql = @sql+' from test3 group by name' print @sqlselect name,sum(case km when '数学' then cj else 0 end)[数学],sum(case km when '英语' then cj else 0 end)[英语],sum(case km when '语文' then cj else 0 end)[语文] from test3 group by name
declare @sql varchar(8000) set @sql = 'select name' select @sql = @sql + ',sum(case km when '+ km+' then cj else 0 end)['+km+']' from (select distinct km from test3) as a select @sql = @sql+' from test3 group by name' print @sqlselect name,sum(case km when 数学 then cj else 0 end)[数学],sum(case km when 英语 then cj else 0 end)[英语],sum(case km when 语文 then cj else 0 end)[语文] from test3 group by name
语法 简单 CASE 函数:CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression END仔细比较一下很简单的!!!!
drop proc proc_sky_blue
go
create proc proc_sky_blue (@tablename varchar(200))
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('表的行数太多了,我转不了',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
endgo
----------------分析
declare @tablename varchar(200)
set @tablename='table1'
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('表的行数太多了,我转不了',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) +')'
select @insertsql
--exec(@insertsql)
fetch next from my_cursor into @col
end
close my_cursor
deallocate my_cursor
select * from #tmp
set nocount off
drop table #tmp
end
end
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)declare @sql varchar(8000)
set @sql = 'select name as 姓名'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test大概这个比较适合,运行结果:
姓名 数学 英语 语文
张三 86 75 80 我要的是这样的结果:
数学 英语 语文
86 75 80
因为我每次只取同一姓名的。我想问的是:
sum(case km when '''+km+''' then cj end) ['+km+']'
我不太明白它的意思,为什么是 '''+km+''' 而不是'+km+'呢?
go
insert into test select'张三','语文',80
union select '张三','数学',86
union select '张三','英语',75declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+ km+''' then cj else 0 end)['+km+']'
from (select distinct km from test3) as a
select @sql = @sql+' from test3 group by name'
print @sqlselect name,sum(case km when '数学' then cj else 0 end)[数学],sum(case km when '英语' then cj else 0 end)[英语],sum(case km when '语文' then cj else 0 end)[语文] from test3 group by name
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '+ km+' then cj else 0 end)['+km+']'
from (select distinct km from test3) as a
select @sql = @sql+' from test3 group by name'
print @sqlselect name,sum(case km when 数学 then cj else 0 end)[数学],sum(case km when 英语 then cj else 0 end)[英语],sum(case km when 语文 then cj else 0 end)[语文] from test3 group by name
简单 CASE 函数:CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
END仔细比较一下很简单的!!!!
楼主多去文档中心和微软中国社区看看,那里有取之不尽的宝贝