sqlserver实现字段名与字段值交叉(原创)

注：仅适合数据量小的表

欢迎大家拍我吧....

有表score

id   name         city                  score
-------------------------------------------------

1     罗毅          重庆                80

2     小廖          杭州                90

3    王文明       开封                99

交叉后的效果为：

fieldname      罗毅       小廖         王文明
----------------------------------------------------

id                    1              2                3

name             罗毅       小廖         王文明

city                  重庆       杭州         开封

score               80           90            99

例子：

exec pro_crosstable 'sore','name'
exec pro_crosstable 'sore','city'

/*
* 过程名称：pro_crosstable
* 过程描述：实现字段名与字段值交叉
* 参数： @tableName 被交叉的表
         @crossFieldName 被交叉的字段
* 创建人：罗毅
* 日期：2012.04.28
*/
Create procedure [dbo].[pro_crosstable](@tableName varchar(50),@crossFieldName varchar(50))
AS

declare @sql nvarchar(4000)
declare @colname varchar(80)
set @sql = 'declare tab_cursor CURSOR FOR select '+@crossFieldName+' from '+@tableName
exec(@sql)
--用临时表模拟数组
create table #Array(name varchar(20)) ON [PRIMARY]
open tab_cursor
fetch next from tab_cursor into @colname
create table #temp(fieldname varchar(10)) ON [PRIMARY]
while @@fetch_status=0
begin
	set @sql = 'alter table #temp add ' + @colname+' varchar(100)'
	exec(@sql)
	insert into #Array values(@colname)
	fetch next from tab_cursor into @colname
end
close tab_cursor
DEALLOCATE tab_cursor


--循环列名
declare col_cursor CURSOR FOR select name from syscolumns where id = (select id from sysobjects where name=@tableName)
declare @index int
declare @count int
declare @value nvarchar(20)
declare @fieldvalue nvarchar(20)
declare @headerName varchar(20)

select @count = count(*) from #Array
open col_cursor
fetch next from col_cursor into @colname
while @@fetch_status =0
begin
    --将字段名插入行
	insert into #temp(fieldname)values(@colname)
	--从数组中查找到元素为index的值
	set @index = 1
	while @index <= @count
	begin
		declare @fsql nvarchar(4000)
		set @fsql = 
		'select @value = '+@crossFieldName+'  from 
		(select top '+cast(@index as varchar)+' * from '+@tableName+') A  where not exists(select * from (select top '+cast(@index-1 as varchar)+' * from '+@tableName+') B where A.'+@crossFieldName+'=B.'+@crossFieldName+')'
		execute sp_executesql @fsql,N'@value nvarchar(20) output',@value output
		set @index = @index + 1
		--更新
		set @fsql = 'select @fieldvalue='+@colname+' from '+@tableName+' where '+@crossFieldName+'='''+@value+''''
		execute sp_executesql @fsql,N'@fieldvalue nvarchar(20) output',@fieldvalue output
		set @sql = 'update #temp set '+@value+'='''+@fieldvalue+''' where fieldname='''+@colname+''''
		exec(@sql)
	end
	fetch next from col_cursor into @colname
end
close col_cursor
DEALLOCATE col_cursor
select * from #temp