Click here to Skip to main content
Rate this: bad
good
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.5K
Edited 4-Dec-12 2:08am
v3
Comments
shadmehr at 4-Dec-12 7:09am
   
what is your DB? (SQL language )? PLSQL (oracle) or SQL Server?
suhelsa at 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
0 Sergey Alexandrovich Kryukov 225
1 CHill60 210
2 Zoltán Zörgő 175
3 Peter Leow 170
4 Richard Deeming 166
0 Sergey Alexandrovich Kryukov 9,278
1 OriginalGriff 6,806
2 Peter Leow 4,432
3 Zoltán Zörgő 3,944
4 Richard MacCutchan 2,782


Advertise | Privacy | Mobile
Web01 | 2.8.150129.1 | Last Updated 21 Dec 2012
Copyright © CodeProject, 1999-2015
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