13,256,716 members (53,038 online)
Rate this:
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

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

26

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
kingsa1.7K
Updated 4-Dec-12 2:08am
v3

what is your DB? (SQL language )? PLSQL (oracle) or SQL Server?
suhelsa 6-Dec-12 0:33am

sqlserver

Rate this:

## 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```

Thanks !
Yogendra Dubey

Top Experts
Last 24hrsThis month
 OriginalGriff 180 F-ES Sitecore 170 Dave Kreskowiak 165 Jochen Arndt 120 CPallini 95
 OriginalGriff 4,079 Karthik Bangalore 2,276 ppolymorphe 1,744 Dave Kreskowiak 1,576 CPallini 1,335