SqlServer 行列转换

1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)

想变成

姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78


declare @sql varchar(8000)
set @sql = 'select name'
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



eg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!


--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A  

 

2:
/*********** 行转列 *****************/
测试：
create table t1 (a int,b int,c int,d int,e int,f int,g int,h int)
insert t1 values(15, 9, 1, 0, 1, 2, 2, 0)

declare @ varchar(8000)
set @=''
select @=@+rtrim(name)+' from t1 union all select ' from syscolumns where id=object_id('t1')
set @=left(@,len(@)-len(' from t1 union all select '))
--print @
exec('select '+@+' from t1')

a
-----------
15
9
1
0
1
2
2
0  

 

3:
将结果矩阵转置。
http://expert.csdn.net/Expert/topic/2390/2390314.xml?temp=.4681055
老衲：


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
end

go
----------------分析
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

评论

1 楼 ifree_0 2009-10-07  

可以不用游标嘛,代码就不贴了,提升点效率三