Try this declare @tbl table(person varchar(50),name varchar(50),value int) insert into @tbl select 'xyz','a',10 union all select 'xyz','b',5 union all select 'xyz','a',15 union all select 'xyz','b',4 union all select 'xyz','c',1 union all select 'xyz','a',5 union all select 'xyz','d',10 union all select 'xyz','a',10 union all select 'abc','a',0 union all select 'abc','b',0 union all select 'abc','c',0 union all select 'abc','d',0
;with cte as
(
select person,name,sum(value) as total from @tbl group by person,name
)
select person,'others' as name , SUM(total) as total from cte where name not in('a')
group by person
union all
select person,'a' as name , SUM(total) as total from cte where name in('a')
group by person
order by person desc
:)
Niladri Biswas