Click here to Skip to main content
12,627,377 members (35,724 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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
Comments
shadmehr 4-Dec-12 7:09am
   
what is your DB? (SQL language )? PLSQL (oracle) or SQL Server?
suhelsa 6-Dec-12 0:33am
   
sqlserver

1 solution

Rate this: bad
 
good
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
  Permalink  

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 | Mobile
Web02 | 2.8.161205.3 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2016
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