ID Arg Cnt Fre Cyle Vist Type
1 A 12 4 530 60 1
1 A 12 4 530 60 2
2 B 12 2 529 40 1
2 B 12 2 529 40 2
3 C 12 1 531 20 1
3 C 12 1 531 20 2
4 D 12 3 531 10 1
4 D 12 3 531 10 2
转换成
ID Arg Cnt Fre1 Cyle1 Vist1 Fre2 Cyle2 Vist2
1 A 12 4 530 60 4 530 60
2 B 12 2 529 40 2 529 40
3 C 12 1 531 20 1 531 20
4 D 12 3 531 10 3 531 10
PS:ID Arg Cnt为固定列, (Fre Cyle Vist)*Type为动态列头
如果Type值有1，2，3 那ABCD各有3条数据 那列头就为
ID Arg Cnt Fre1 Cyle1 Vist1 Fre2 Cyle2 Vist2 Fre3 Cyle3 Vist3
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Arg] varchar(1),[Cnt] int,[Fre] int,[Cyle] int,[Vist] int,[Type] int)
insert [tb]
select 1,'A',12,4,530,60,1 union all
select 1,'A',12,4,530,60,2 union all
select 2,'B',12,2,529,40,1 union all
select 2,'B',12,2,529,40,2 union all
select 3,'C',12,1,531,20,1 union all
select 3,'C',12,1,531,20,2 union all
select 4,'D',12,3,531,10,1 union all
select 4,'D',12,3,531,10,2
go---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when type='+ltrim(type)+' then Fre else 0 end) as [Fre'+ltrim(type)+'],'
+'max(case when type='+ltrim(type)+' then Cyle else 0 end) as [Cyle'+ltrim(type)+'],'
+'max(case when type='+ltrim(type)+' then Vist else 0 end) as [Vist'+ltrim(type)+']'
from
(select distinct type from tb) tset @sql='select ID,Arg,Cnt,'+@sql+' from tb group by ID,Arg,Cnt'--print @sqlexec (@sql)---结果---
ID Arg Cnt Fre1 Cyle1 Vist1 Fre2 Cyle2 Vist2
----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 A 12 4 530 60 4 530 60
2 B 12 2 529 40 2 529 40
3 C 12 1 531 20 1 531 20
4 D 12 3 531 10 3 531 10