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 '[email protected]
exec sp_executesql @str,N '@rows int output ',@rows output
set @str=N 'select @cols=count(1) from information_schema.columns where table_name= ' ' '[email protected]+N ' ' ' '
exec sp_executesql @str,N '@cols int output ',@cols output
create table t(col1 varchar(100))
set @i=2
while @i <[email protected]
begin
set @str=N 'alter table t add col '+cast(@i as varchar(2))+N ' varchar(100) '
exec sp_executesql @str
set @[email protected]+1
end
set @i=1
while @i <[email protected]
begin
set @str=N 'select @colname=column_name from information_schema.columns
where table_name= ' ' '[email protected]+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= '[email protected]+N ' from '[email protected]+N ' where '[email protected]+N ' not in(select top '+cast(@j as nvarchar(10))+ ' '[email protected]+N ' from '[email protected]+N ' Order by '[email protected]+N ') '
--set @str=N 'select top 1 @[email protected]+ '[email protected]+N ' from '[email protected]