Pivot table in asp.net gridview control

[dha lak]

Hi,


I have 4 tables in my database. i want to create pivot table using them in a gridview.

1) Category
CategoryID CategoryName
1 Milk
2 Nuts
3 Fruits
4 Vegetables
5 Cakes


2) SubCategory
SubCategoryID CategoryID SubCategoryName
1 1 Soya
2 2 Almonds
3 2 Cashew
4 3 Apple
5 3 Orange
6 3 Grapes
7 3 Cherry



3) Product
ProductID ProductCode ProductName SubCategoryID Price
1000 A1 Cashew1 3 5
2000 B1 Cashew2 3 5
3000 C1 Cashew3 3 5
4000 D1 BlackGrapes 6 10
5000 E1 GreenGrapes 6 10
6000 F1 RoseGrapes 6 10



4) Sales
ID Date ProductID Qty Cost
1 25-Oct-11 1000 1 5
2 25-Oct-11 2000 1 5
3 25-Oct-11 3000 1 5
4 26-Oct-11 1000 2 10
5 26-Oct-11 2000 2 10
6 26-Oct-11 3000 2 10

------------------------------------------------------------

These are the tables in my DB.

I use the following query to display a pivot table



DECLARE @var nvarchar(500)
DECLARE @str nvarchar(2000)
SELECT @var =
STUFF(
(
select distinct ',[' + ltrim(rtrim(cast(CategoryName as char(10))))+ ']'
from Category
For XML Path('')
),
1,1,'')


SELECT @str = N'
select *
from (
select S.Date, S.Cost, C.CategoryName
from Sales S
left join Product P on S.ProductID = P.ProductID
left join Subcategory SC on SC.SubCategoryID = P.SubCategoryID
left join Category C on C.CategoryID = SC.CategoryID
) DataTable
PIVOT (
Sum(DataTable.Cost)
FOR DataTable.CategoryName IN (' + @var + ')
) PivotTable'



exec sp_executesql @str

It works fine.

It produces the following output.

Date | Cakes | Fruits | Milk | Nuts |Vegetables
2011-10-25 | NULL | NULL | NULL | 15.0000 | NULL
2011-10-26 | NULL | NULL | NULL | 30.0000 | NULL




But, i want to display this in my web page in a GridView as hierarchial data, just like how it will be displayed in a excel pivot table.


That is,



25-Oct-11 15
----Nuts 15
---------Cashew3 15
---------1000 5
---------2000 5
---------3000 5

26-Oct-11 30
----Nuts 30
---------Cashew3 30
---------1000 10
---------2000 10
---------3000 10
Grand Total 45




-----------
I checked this link to display a treeview inside a gridview.
http://www.codeproject.com/KB/aspnet/GridViewTreeView.aspx?msg=2484303

Can anyone please suggest me how to do this to produce a pivot table in a gridview like control, that will be exactly like how pivot table looks in excel spreadsheet.

Thanks in advance

[dilipkakadiya]

hope this help


http://aspnetvisual.blogspot.com/201...ric-pivot.html

[dha lak]

Thanks for the reply.

I will check this link