SQL ½«ÁÐת»»ÎªÐУ¬Ï൱ÓÚ½«±í½á¹¹Ðýת90¶È

½«ÁÐת»»ÎªÐУ¬Ï൱ÓÚ½«±í½á¹¹Ðýת90¶È
T_Student ±í
Stud_ID
Sex
Name
1
ÄÐ
Tom
2
Å®
Anne
3
ÄÐ
Jack
 
Ö´ÐÐ: Exec proColumnToRow ’T_Student’,’Name’,’ New_ID’
 
ת»»ºóµÄ±í
 
New_ID
Tom
Anne
Jack
Stud_ID
1
2
3
Sex
ÄÐ
Å®
ÄÐ
 
--½«ÁÐת»»ÎªÐÐ
create proc proColumnToRow
       @tbname sysname, --Òª´¦ÀíµÄ±íÃû
       @fdname sysname, --ijһÁеÄֵת»»ÎªÐ±íµÄÁÐ
       @new_fdname sysname='' --½«Ô´±íµÄÁÐת»»ÎªÐÂÁеÄÖµ¡£@new_fdnameΪÐÂÁеÄÁÐÃû
as
declare @s1 varchar(8000) , @s2 varchar(8000),
        @s3 varchar(8000) , @s4 varchar(8000),
        @s5 varchar(8000) , @i varchar(10)
select
 @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select
 @s1 = @s1 + ',@' + @i + ' varchar(8000)',
       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
       else @new_fdname + '=' end + '''''' + name + '''''''',
       @s3 = @s3 + 'select
 @' + @i + '=@' + @i + '+'',['' + cast(' + @fdname +
       ' as varchar)+'']=''''''+cast([' + name + '] as varchar)+'''''''' from [' + @tbname + ']',
       @s4 = @s4 + ',@' + @i + '=''select
 ''+@' + @i,
       @s5 = @s5 + '+'' union all ''+@' + @i,
       @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname
 
select
 @s1=substring(@s1,2,8000),
       @s2=substring(@s2,2,8000),
       @s4=substring(@s4,2,8000),
       @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'selec