Group by Rollup Output Format

• shohelr2003

  Hall of Fame

  Points: 3535

  More actions

  June 17, 2013 at 11:08 pm

  #295451

  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