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(cas t(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.SubCategoryI D = P.SubCategoryID
left join Category C on C.CategoryID = SC.CategoryID
) DataTable
PIVOT (
Sum(DataTable.C ost)
FOR DataTable.Categ oryName 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/GridViewTreeVie w.aspx?msg=2484 303
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
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(cas t(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.SubCategoryI D = P.SubCategoryID
left join Category C on C.CategoryID = SC.CategoryID
) DataTable
PIVOT (
Sum(DataTable.C ost)
FOR DataTable.Categ oryName 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/GridViewTreeVie w.aspx?msg=2484 303
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
Comment