Hi Suhelsa,
Nice Question
I have done this in 3 Steps
1) Extract Data into Temporary table #temp
Select * into #temp from TblCountry
2) Insert Total Population Rows in Temporary Table
insert into #temp
select country,''state, SUM(Population)Population
from #temp
group by country
3) Set Rank Number on the basis of contry and Order by State
select Rank() over (Partition By #temp.Country order by #temp.Country , state desc) row , *
into #tempOutput
from #temp
4) Update #tempOutput set Country Name ='' where Row > 1
update #tempOutput set Country ='' where ROW > 1
5) Select from Output Table and drop Temporary table Created
Select * from #tempOutput
drop #tempOutput
drop #temp
Please vote for My Solution
Thanks !
Yogendra Dubey