这是一张存储过程参数表
ID对应的是存储过程的ID,
index 对应的是参数的序号,
param 参数的名字ID index param
1 1 @Accounts
1 2 @Pwd
1 3 @LoginIP
1 4 @OutPutValue
2 1 @Accounts
3 1 @UserId
11 1 @UserID
12 1 @ServerID
13 1 @ServerID转换成:ID param1 param2 param3 param4
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID存储过程ID和它的所有参数
ID对应的是存储过程的ID,
index 对应的是参数的序号,
param 参数的名字ID index param
1 1 @Accounts
1 2 @Pwd
1 3 @LoginIP
1 4 @OutPutValue
2 1 @Accounts
3 1 @UserId
11 1 @UserID
12 1 @ServerID
13 1 @ServerID转换成:ID param1 param2 param3 param4
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID存储过程ID和它的所有参数
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,[index] int,param varchar(12))
insert into #T
select 1,1,'@Accounts' union all
select 1,2,'@Pwd' union all
select 1,3,'@LoginIP' union all
select 1,4,'@OutPutValue' union all
select 2,1,'@Accounts' union all
select 3,1,'@UserId' union all
select 11,1,'@UserID' union all
select 12,1,'@ServerID' union all
select 13,1,'@ServerID'
godeclare @sql varchar(8000)
set @sql='select ID'
select @sql=@sql+',max(case when [index]='+ltrim([index])+' then param end) [param'+ltrim([index])+']'
from #T group by [index]exec (@sql+' from #T group by ID')go
drop table #T/*
ID param1 param2 param3 param4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts NULL NULL NULL
3 @UserId NULL NULL NULL
11 @UserID NULL NULL NULL
12 @ServerID NULL NULL NULL
13 @ServerID NULL NULL NULL
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,[index] int,param varchar(12))
insert into #T
select 1,1,'@Accounts' union all
select 1,2,'@Pwd' union all
select 1,3,'@LoginIP' union all
select 1,4,'@OutPutValue' union all
select 2,1,'@Accounts' union all
select 3,1,'@UserId' union all
select 11,1,'@UserID' union all
select 12,1,'@ServerID' union all
select 13,1,'@ServerID'
godeclare @sql varchar(8000)
set @sql='select ID'
select @sql=@sql+',max(case when [index]='+ltrim([index])+' then param else '''' end) [param'+ltrim([index])+']'
from #T group by [index]exec (@sql+' from #T group by ID')go
drop table #T/*
ID param1 param2 param3 param4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID */
insert into tb select 1,1,'@Accounts'
insert into tb select 1,2,'@Pwd'
insert into tb select 1,3,'@LoginIP'
insert into tb select 1,4,'@OutPutValue'insert into tb select 2,1,'@Accounts'
insert into tb select 3,1,'@UserId'
insert into tb select 11,1,'@UserID'
insert into tb select 12,1,'@ServerID'
insert into tb select 13,1,'@ServerID'declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when [index]='''+ltrim([index])+''' then param else '''' end) as [param'+ltrim([index])+']'
from (select distinct [index] from tb)t
exec('select id,'+@sql+' from tb group by id')
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID
go
Declare @1 Table(id int,[index] int,param nvarchar(50))
Insert Into @1
Select 1, 1 ,'@Accounts' Union All
Select 1, 2 ,'@Pwd' Union All
Select 1, 3 ,'@LoginIP' Union All
Select 1, 4 ,'@OutPutValue' Union All
Select 2, 1 ,'@Accounts' Union All
Select 3, 1 ,'@UserId' Union All
Select 11, 1 ,'@UserID' Union All
Select 12, 1 ,'@ServerID' Union All
Select 13, 1 ,'@ServerID' Select id,
[1] As param1,
[2] As param2,
[3] As param3,
[4] As param4
From (Select * From @1) P
Pivot
(Max(param) for [index] in([1],[2],[3],[4])) X(9 行受影响)
id param1 param2 param3 param4
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts NULL NULL NULL
3 @UserId NULL NULL NULL
11 @UserID NULL NULL NULL
12 @ServerID NULL NULL NULL
13 @ServerID NULL NULL NULL
--Sql server 2000 静态SQL。
select id,
max(case index when 1 then param else '' end) param1,
max(case index when 2 then param else '' end) param2,
max(case index when 3 then param else '' end) param3,
max(case index when 4 then param else '' end) param4
from tb
group by id
select id,
max(case [index] when 1 then param else '' end) param1,
max(case [index] when 2 then param else '' end) param2,
max(case [index] when 3 then param else '' end) param3,
max(case [index] when 4 then param else '' end) param4
from tb
group by id--Sql server 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case [index] when ''' + cast([index] as varchar) + ''' then param else '''' end) [param' + cast([index] as varchar) + ']'
from (select distinct [index] from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
insert into tb
select 1,1,'@Accounts' union all
select 1,2,'@Pwd' union all
select 1,3,'@LoginIP' union all
select 1,4,'@OutPutValue' union all
select 2,1,'@Accounts' union all
select 3,1,'@UserId' union all
select 11,1,'@UserID' union all
select 12,1,'@ServerID' union all
select 13,1,'@ServerID'
go--Sql server 2000 静态SQL。
select id,
max(case [index] when 1 then param else '' end) param1,
max(case [index] when 2 then param else '' end) param2,
max(case [index] when 3 then param else '' end) param3,
max(case [index] when 4 then param else '' end) param4
from tb
group by id
/*
id param1 param2 param3 param4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID
*/--Sql server 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case [index] when ''' + cast([index] as varchar) + ''' then param else '''' end) [param' + cast([index] as varchar) + ']'
from (select distinct [index] from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)
/*
id param1 param2 param3 param4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID
*/drop table tb
insert into tb
select 1,1,'@Accounts' union all
select 1,2,'@Pwd' union all
select 1,3,'@LoginIP' union all
select 1,4,'@OutPutValue' union all
select 2,1,'@Accounts' union all
select 3,1,'@UserId' union all
select 11,1,'@UserID' union all
select 12,1,'@ServerID' union all
select 13,1,'@ServerID'
go--Sql server 2005 静态SQL。
select * from (select * from tb) a pivot (max(param) for [index] in ([1],[2],[3],[4])) b
/*
ID 1 2 3 4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts NULL NULL NULL
3 @UserId NULL NULL NULL
11 @UserID NULL NULL NULL
12 @ServerID NULL NULL NULL
13 @ServerID NULL NULL NULL(6 行受影响)
*/--Sql server 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + cast([index] as varchar) from tb group by [index]
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(param) for [index] in (' + @sql + ')) b')
/*
ID 1 2 3 4
----------- ------------ ------------ ------------ ------------
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts NULL NULL NULL
3 @UserId NULL NULL NULL
11 @UserID NULL NULL NULL
12 @ServerID NULL NULL NULL
13 @ServerID NULL NULL NULL(6 行受影响)
*/drop table tb
declare @paramcount int,--参数个数
@i int,@lstr varchar(8000),@istr varchar(10)
select @paramcount=max([index]) from tp
set @i=1
set @lstr='select id'
while @i<=@paramcount
begin
set @istr=convert(varchar(10),@i)
set @lstr=@lstr+',param'+@istr
+'=case when exists(select * from tp where id=a.id and [index]='+@istr+') then '
+' (select top 1 param from tp where id=a.id and [index]='+@istr+') else '''' end'
set @i=@i+1
end
set @lstr=@lstr+' from (select distinct id from tp) a order by a.id '
exec (@lstr)
declare @paramcount int,--参数个数
@i int,@lstr varchar(8000),@istr varchar(10)
select @paramcount=max([index]) from tp
set @i=1
set @lstr='select id'
while @i<=@paramcount
begin
set @istr=convert(varchar(10),@i)
set @lstr=@lstr+',param'+@istr
+'=case when exists(select * from tp where id=a.id and [index]='+@istr+') then '
+' (select top 1 param from tp where id=a.id and [index]='+@istr+') else '''' end'
set @i=@i+1
end
set @lstr=@lstr+' from (select distinct id from tp) a order by a.id '
exec (@lstr)
CSDN群 招募中……Software Coffee House -----群名