Click here to Skip to main content
13,256,716 members (53,038 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hello, I have table like this

country | State | population(in Millions)
India | Ap | 5
India | Mp | 6
Us | mUscha| 10
US | NewYoek| 6
Us | mUscha| 10

i want out put lile

country | State | population(in Millions)
India | Ap | 5
| Mp | 6

Us | mUscha| 10
| NewYoek| 6
| mUscha| 10


country display only in first row and total of population display particlular country end group next row

can u guide or send any snippets
Posted 4-Dec-12 2:06am
Updated 4-Dec-12 2:08am
shadmehr 4-Dec-12 7:09am
what is your DB? (SQL language )? PLSQL (oracle) or SQL Server?
suhelsa 6-Dec-12 0:33am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Hi Suhelsa,
Nice Question

I have done this in 3 Steps

1) Extract Data into Temporary table #temp

assuming Your Table Name as - TblCountry
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100