--> 测试数据: #T1
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 */
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')id param1 param2 param3 param4
1 @Accounts @Pwd @LoginIP @OutPutValue
2 @Accounts
3 @UserId
11 @UserID
12 @ServerID
13 @ServerID
set @sql=''select @sql=@sql+',[para'+rtrim([index])+']=max(case [index] when '+rtrim([index])+' then param end)'
from (select distinct [index] from 表) tset @sql='select ID'+@sql+' from 表 group by ID'exec(@sql)
insert into tname values(1 ,1,'@Accounts ')
insert into tname values(1 ,2,'@Pwd ')
insert into tname values(1 ,3,'@LoginIP ')
insert into tname values(1 ,4,'@OutPutValue')
insert into tname values(2 ,1,'@Accounts ')
insert into tname values(3 ,1,'@UserId ')
insert into tname values(11,1,'@UserID ')
insert into tname values(12,1,'@ServerID ')
insert into tname values(13,1,'@ServerID ')
go
declare @sql varchar(8000)
set @sql=''select @sql=@sql+',[para'+rtrim([index])+']=max(case [index] when '+rtrim([index])+' then param end)'
from (select distinct [index] from tname) tset @sql='select ID'+@sql+' from tname group by ID'exec(@sql)
/*
ID para1 para2 para3 para4
----------- -------------------- -------------------- -------------------- --------------------
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
*/
go
drop table tname
go