求助!求助!求助!求助!

[jettylee]

怎样写一个存储过程建一个临时表或者视图
将一个表的行和列互换????
比如有个表
字段: a b c
数据: 1 2 3
4 5 6
换成
a 1 4
b 2 5
c 3 6

[filebat]

想做啊.
不过要睡觉了.
先占一楼再说.

[jettylee]

多谢楼上两位~~~ 一定给分的！
自己再up一下。。。

[xdhou]

行列互换的问题已经有很多人问过了,查一下以前的贴.

[天地客人]

帮你UP

[Well]

--引用鄒大哥
--給你這個例子
create table tb( O char(1),X varchar(10),Y varchar(10),Z varchar(10))
insert tb select 'A','x1','y1','z1'
union all select 'B','x2','y2','z2'
union all select 'C','x3','y3','z3'
go
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+'=''O='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[''+cast([O] as varchar)+'']=''''''+cast(['+name+'] as varchar)+'''''''''
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('tb')=id and colid>1
order by colid
select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,5,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')
exec('declare '+@s1+'
select '+@s2+'
select '+@s3+'
from tb
select '+@s4+'
exec('+@s5+')')
/* print @s1: @0 varchar(8000),@1 varchar(8000),@2 varchar(8000)
print @s2: @0='O=''X''',@1='O=''Y''',@2='O=''Z'''
print @s3: @0=@0+',['+cast([O] as varchar)+']='''+cast([X] as varchar)+''''
,@1=@1+',['+cast([O] as varchar)+']='''+cast([Y] as varchar)+''''
,@2=@2+',['+cast([O] as varchar)+']='''+cast([Z] as varchar)+''''
print @s4: @0='select '+@0,@1='select '+@1,@2='select '+@2
print @s5: @0+' union all '+@1+' union all '+@2 */
--刪除測試環境
drop table tb
/*--顯示結果
O A B C
---- ---- ---- ----
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3
（所影的行數只有3 行）
--*/

[vivianfdlpw]

--创建测试环境
create table tb( a int,b int,c int)
insert tb select 1,2,3
union all select 4,5,6
go


declare @sql nvarchar(2000)
declare @cols nvarchar(200)
declare @tmp1 nvarchar(1000)
declare @tmp2 nvarchar(1000)
declare @tmp3 nvarchar(1000)
set @tmp1=''
set @tmp2=''
set @tmp3=''
set @cols=N'select @tmp=@tmp+''[''+cast(a as varchar)+'']''+'' varchar(20),'' from tb'
exec sp_executesql @cols,N'@tmp nvarchar(1000) output',@tmp1 output
set @tmp1=stuff(@tmp1,len(@tmp1),1,'')
set @cols=N'select @tmp=@tmp+''''+cast(b as varchar)+''''+'','' from tb'
exec sp_executesql @cols,N'@tmp nvarchar(1000) output',@tmp2 output
set @tmp2=stuff(@tmp2,len(@tmp2),1,'')
set @cols=N'select @tmp=@tmp+''''+cast(c as varchar)+''''+'','' from tb'
exec sp_executesql @cols,N'@tmp nvarchar(1000) output',@tmp3 output
set @tmp3=stuff(@tmp3,len(@tmp3),1,'')
set @sql=N'create table ##AAAAAA'+
N'('+
N'[a] varchar(20),'+@tmp1+
N')'+
N' insert ##AAAAAA '+
N' select ''b'','+@tmp2+' union all '+
N' select ''c'','+@tmp3+
N' select * from ##AAAAAA'+
N' drop table ##AAAAAA'
--print @sql
exec(@sql)

--刪除测试环境
drop table tb

--结果
/*
a 1 4
------------------------
b 2 5
c 3 6

*/