select [A']=min(A),[B']=max(B),[C']=C,number=count(*) from T group by C
select 'a1' as A,'b1' as B,'c1' as C,'t1' as Type into #t union all select 'a2','b2','c1','t1' union all select 'a3','b3','c1','t1' union all select 'a4','b4','c2','t2' union all select 'a5','b5','c2','t2' union all select 'a6','b6','c3','t3' select min(A) as A,max(B) as B, C,count(C) as number from #t group by Cdrop table #t
from T group by C
union all
select 'a2','b2','c1','t1'
union all
select 'a3','b3','c1','t1'
union all
select 'a4','b4','c2','t2'
union all
select 'a5','b5','c2','t2'
union all
select 'a6','b6','c3','t3' select min(A) as A,max(B) as B, C,count(C) as number from #t group by Cdrop table #t