June 17, 2013 at 11:08 pm
Dear,
I execute the following query and get the result.
select ISNULL([State],'Whole State') AS [State],
ISNULL(City,'All City') AS City, SUM([Population]) AS [Population]
from tblPopulation
group by rollup([State], City)
StateCityPopulation
-------------------------------
DelhiEast Delhi9
DelhiNorth Delhi5.5
DelhiSouth Delhi8
DelhiWest Delhi7.5
DelhiAll City30
KarnatakBangalore9.5
KarnatakBelur2.5
KarnatakManipal1.5
KarnatakAll City13.5
MaharastraMumbai30
MaharastraNagpur11
MaharastraNashik6.5
MaharastraPune20
MaharastraAll City67.5
Whole StateAll City111
You See in [State] column, Delhi is repeated 5 times, Karnatak is repeated 4 times and Maharastra is repeated 5 times.
But I require that Delhi, Karnatak, Maharastra will appear once on the top row. My desired output would look like the following.
StateCityPopulation
-------------------------------
DelhiEast Delhi9
North Delhi5.5
South Delhi8
West Delhi7.5
All City30
KarnatakBangalore9.5
Belur2.5
Manipal1.5
All City13.5
MaharastraMumbai30
Nagpur11
Nashik6.5
Pune20
All City67.5
Whole StateAll City111
Please help me to do it.
Rgds,
Akbar
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply