GROUPING SETS in MSSQL 2008 is an Extension to “Group By” and Gives ease of use and flexibility to define grouping expressions as compared to “RollUp” and “Cube”.
The advantage of using GROUPING SETS is that you can define grouping expressions in Grouping Sets.
SELECT CASE WHEN CountryName IS Null THEN 'All Country' ELSE CountryName End AS CountryName,
CASE WHEN StateName Is Null THEN 'Total' ELSE StateName END AS StateName,
FROM CityMaster Group By GROUPING Sets
Order by CountryName desc,StateName Desc