将列转换为行,相当于将表结构旋转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, --某一列的值转换为新表的列
@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 + 'select ' + @s2 + @s3 + 'select ' + @s4 + ' exec(' + @s5 + ')')
这里用到两个知识点:
1. Select name from syscolumns where object_id(表名)=id and name<>不参与的列名 来找到此表的所有列名
2. 使用 union all 来拼接每一行数据
Select New_ID=’Stud_ID’, Tom=’1’, Anne=’2’, Jack=’3’
Union all
Select New_ID=’Sex’, Tom=’ 男’, Anne=’ 女’, Jack=’男’
这种方式适合于小数据,大数据表不推荐。