一个行列互转的存储过程问题
if object_id( 'tb ') is not null
drop table tb
go
create table tb(
ID int,
BusMan varchar(10),
BusMan2 varchar(10),
BusMoney int)
insert into tb values(1, '郭芙蓉 ', 'null ',100)
insert into tb values(2, '佟相玉 ', 'null ',100)
insert into tb values(3, '郭芙蓉 ', '佟相玉 ',100)
insert into tb values(4, '郭芙蓉 ', '王强 ',100)
insert into tb values(5, '王强 ', 'null ',100)
insert into tb values(6, '张飞 ', 'null ',100)
if object_id( 'row_col_wrap ') is not null
drop proc row_col_wrap
go
create proc row_col_wrap
@tablename nvarchar(128)=null,@orderCol nvarchar(128)
AS
declare @rows int,@cols int
declare @str nvarchar(4000)
declare @i int,@j int
declare @colname varchar(50)
declare @value varchar(8000)
declare @r varchar(8000)
set @str=N 'select @rows=count(1) from '+@tablename
exec sp_executesql @str,N '@rows int output ',@rows output
set @str=N 'select @cols=count(1) from information_schema.columns where table_name= ' ' '+@tablename+N ' ' ' '
exec sp_executesql @str,N '@cols int output ',@cols output
create table t(col1 varchar(100))
set @i=2
while @i <=@rows
begin
set @str=N 'alter table t add col '+cast(@i as varchar(2))+N ' varchar(100) '
exec sp_executesql @str
set @i=@i+1
end
set @i=1
while @i <=@cols
begin
set @str=N 'select @colname=column_name from information_schema.columns
where table_name= ' ' '+@tablename+N ' ' ' and Ordinal_Position= '+cast(@i as nvarchar(10))
exec sp_executesql @str,N '@colname varchar(50) output ',@colname output
set @r= ' '
set @j=0
while @j <@rows
begin
set @str=N 'select top 1 @value= '+@colname+N ' from '+@tablename+N ' where '+@colname+N ' not in(select top '+cast(@j as nvarchar(10))+ ' '+@colname+N ' from '+@tablename+N ' Order by '+@orderCol+N ') '
--set @str=N 'select top 1 @value=@value+ '+@colname+N ' from '+@tablename
--print @str
select @value= ' '
exec sp_executesql @str,N '@value varchar(8000) output ',@value output
select @r=@r+ ' ' ' '+@value+ ' ' ', '
set @j=@j+1
end
print @r
if right(@r,1)= ', '
set @r=left(@r,len(@r)-1)
set @str=N 'insert into t select '+@r
exec (@str)
set @i=@i+1
end
select * from t
drop table t
go
exec row_col_wrap 'tb ', 'ID '
这个存储过程有什么问题,为什么得到的结果总错误
[解决办法]
你这个问题可是上午那个问题?
如果是,其行列转换没这么复杂吧?
你把数据和结果帖出来我看看.(看代码确实头疼)
