Creating effective report using Roll UP in SQL SERVER 2008

This example shows you how to use ROLL UP to create a report that return sum of stock,sale and amount on the basis of Product_Group and Product_Code.

drop table [#tmp]
CREATE TABLE [dbo].[#tmp](
    [Product_Group] [varchar](1000) NULL,
    [Product_Code] [varchar](1000) NULL,
    [Product_Name] [varchar](1000) NULL,
    [Stock] [int] NULL,
    [Sale] [int] NULL,
    [Amt] [numeric](18, 0) NULL
)


 Insert Into #tmp (Product_Group, Product_Code, Product_Name, Stock, Sale, Amt)
 Values ('Stationary', 'A12', 'Pencil', 63, 30, 300),
 ('Stationary', 'A13', 'Pen', 83, 61, 534),
 ('Furniture', 'F11', 'Table', 113, 105, 68000),
 ('Furniture', 'F12', 'Chair', 62, 55, 55234)

 --select * from #tmp

; with result
as
(
 select Product_Group, Product_Code, Product_Name, Sum(Stock) as Stock, Sum(Sale) as Sale, Sum(Amt) as Amt from #tmp
group by ROLLUP
(Product_Group,Product_code,Product_name)
)

select isnull(Product_Group,'Grand Total:'),
(case when product_group is null and product_code is null then '' else isnull(Product_Code,'Sub-totals:') end) Product_code ,
isnull(Product_Name,'') as Product_Name, Stock, Sale, Amt
 from result
where (product_name is not null and product_code is not null)
or
(product_name is null and product_code is null )

this is the result set returns for this query