首页 > 代码库 > sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
ALTER procedure dbo.CommonRowToCol @sql nvarchar(4000) as begin --必须包含colname列和result列(不区分大小写), --除colname列和result列 其余各列均会作为判别记录唯一性的条件 --使用时,只需将要作为列名的字段定义为colname,将欲显示的列定义为result即可 --如select student as 学生 ,course as colname,score result from class create table #temp([序号] int IDENTITY(1,1)) declare @sqlTemp nvarchar(4000),@sql_select nvarchar(1000),@temp nvarchar(500) declare @sql_All nvarchar(4000),@sql_Table nvarchar(1000),@sql_declare nvarchar(1000),@sql_Fetch nvarchar(1000) , @sql_id nvarchar(1000),@sql_id2 nvarchar(1000),@sql_insert nvarchar(1000),@sql_value nvarchar(1000) set @sql_select = ‘‘ set @sql_All = ‘‘ set @sql_Table =‘‘ set @sql_declare = ‘‘ set @sql_Fetch = ‘‘ set @sql_id = ‘‘ set @sql_id2 = ‘‘ set @sql_insert=‘‘ set @sql_value = ‘‘ set @sql = lower(LTRIM(@sql)) set @sqlTemp = @sql set @sqlTemp = ‘ select top 0 * into #tempTbl from (‘+@sqlTemp+‘)z ‘+ ‘ select @temp = Name+‘‘,‘‘+@temp from ( select top 1111 name from tempdb..syscolumns where id = object_id(N‘‘tempdb..#tempTbl‘‘) order by colorder )z ‘ EXECUTE sp_executesql @sqlTemp, N‘@temp nvarchar(1000) output‘,@sql_select output while charindex(‘,‘,@sql_select) > 0 begin select @temp = lower(LEFT(@sql_select,charindex(‘,‘,@sql_select)-1)) select @sql_select = stuff(@sql_select,1,charindex(‘,‘,@sql_select),‘‘) if (@temp <> ‘colname‘) and (@temp<>‘result‘) begin set @sql_Table = ‘ [‘+@temp+‘] nvarchar(1000),‘ + @sql_Table set @sql_insert = ‘ [‘+@temp+‘],‘ + @sql_insert set @sql_value =‘isnull(@‘+@temp+‘,‘‘‘‘),‘+@sql_value set @sql_id = ‘ [‘+@temp+‘] = isnull(@‘+@temp+‘,‘‘‘‘) and‘ + @sql_id set @sql_id2 = ‘ [‘+@temp+‘] = isnull(‘‘‘‘‘‘+@‘+@temp+‘+‘‘‘‘‘‘ ,‘‘‘‘‘‘‘‘) and‘ + @sql_id2 end; set @sql_declare = ‘ @‘+@temp+‘ nvarchar(1000),‘+@sql_declare set @sql_Fetch = ‘ @‘+@temp+‘,‘+@sql_Fetch end; set @sql_Table = ‘ALTER TABLE #temp ADD ‘ +left(@sql_Table,len(@sql_table)-1)+‘ ‘ set @sql_declare = ‘declare @sqlTemp nvarchar(4000),‘ +left(@sql_declare,len(@sql_declare)-1) + ‘ ‘ set @sql_Fetch = left(@sql_Fetch,len(@sql_fetch)-1) + ‘ ‘ set @sql_id = left(@sql_id,len(@sql_id)-3) + ‘ ‘ set @sql_id2 = left(@sql_id2,len(@sql_id2)-3) + ‘ ‘ set @sql_insert = left(@sql_insert,len(@sql_insert)-1) + ‘ ‘ set @sql_value = left(@sql_value,len(@sql_value)-1) + ‘ ‘ set @sql_All = @sql_declare + ‘ Declare myCur Cursor For ‘+ @sql + ‘ Open myCur Fetch NEXT From myCur Into ‘+ @sql_Fetch+ ‘ While @@fetch_status=0 Begin ‘+ ‘ if not exists(select * from tempdb..syscolumns where id = object_id(N‘‘tempdb..#temp‘‘) and name = @colName) begin set @sqlTemp =‘‘alter table #temp add [‘‘+@colName+‘‘] nvarchar(4000) ‘‘ exec(@sqlTemp) end if not exists(select * from #temp where ‘+@sql_id+‘) begin insert into #temp(‘+@sql_insert+‘) values(‘+@sql_value+‘) end set @sqlTemp =‘‘ update #temp set [‘‘+@colName+‘‘] = isnull(‘‘‘‘‘‘+@result+‘‘‘‘‘‘,‘‘‘‘‘‘‘‘) where ‘ + @sql_id2+‘‘‘ exec(@sqlTemp) Fetch NEXT From myCur Into ‘+ @sql_Fetch+ ‘end Close myCur Deallocate myCur select * from #temp ‘ exec (@sql_Table) exec(@sql_All) end
sql2000行转列 转过来的测试完也不知那个网站去哪了 没法写出处了
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。