首页 > 代码库 > 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行转列 转过来的测试完也不知那个网站去哪了 没法写出处了