create table t (aa varchar(10),bb varchar(10),cc int) insert into t select 'aaa1','a1',1 union all select 'aaa2','a1',2 union all select 'aaa3','a1',3 union all select 'aaa1','a2',4 union all select 'aaa2','a2',5 union all select 'aaa3','a2',8 union all select 'aaa1','a3',2 union all select 'aaa2','a3',1 union all select 'aaa3','a3',7declare @sqlt varchar(8000) set @sqlt='' select @sqlt=@sqlt+',['+bb+']=sum(case when bb='''+[bb]+''' then cc else 0 end) ' from (select distinct bb from t) o set @sqlt='select a.aa,bb=(select top 1 bb from t where aa=a.aa order by cc),cc=min(cc)'+@sqlt+' from t a group by aa'exec (@sqlt)drop table t
insert into t
select 'aaa1','a1',1
union all
select 'aaa2','a1',2
union all
select 'aaa3','a1',3
union all
select 'aaa1','a2',4
union all
select 'aaa2','a2',5
union all
select 'aaa3','a2',8
union all
select 'aaa1','a3',2
union all
select 'aaa2','a3',1
union all
select 'aaa3','a3',7declare @sqlt varchar(8000)
set @sqlt=''
select @sqlt=@sqlt+',['+bb+']=sum(case when bb='''+[bb]+''' then cc else 0 end) '
from (select distinct bb from t) o set @sqlt='select a.aa,bb=(select top 1 bb from t where aa=a.aa order by cc),cc=min(cc)'+@sqlt+' from t a group by aa'exec (@sqlt)drop table t