Pivot table in asp.net GridView - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Pivot table in asp.net GridView

dhanlakdhanlak Posts: 4Member
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

Sign In or Register to comment.