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 outputset @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
endset @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
goexec row_col_wrap 'tb','ID'这个存储过程有什么问题,为什么得到的结果总错误
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 outputset @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
endset @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
goexec row_col_wrap 'tb','ID'这个存储过程有什么问题,为什么得到的结果总错误
如果是,其行列转换没这么复杂吧?
你把数据和结果帖出来我看看.(看代码确实头疼)
所有代码都在上面了.
各位复制到查询分析器里运行看一下..就知道是什么样了.