Pivot Table is a way to quickly summarize data
and information from your database and also an useful way to organize
and present data in your reports.
Usually in SQL server we can use the PIVOT statement in SQL SELECT, something
like this:
SELECT
<non-pivoted
column>,
[first pivoted column]
AS <column
name>,
[second pivoted column]
AS <column
name>,
...
[last pivoted column]
AS <column
name>
FROM
(<SELECT
query that produces the data>)
AS
<alias for the
source query>
PIVOT
(
<aggregation
function>(<column
being aggregated>)
FOR
[<column that contains
the values that will become column headers>]
IN
(
[first pivoted column], [second pivoted column],
...
[last pivoted column])
)
AS <alias
for the pivot
table>
<optional
ORDER BY clause>;
Imagine to have a classic
Northwind database with Orders table, table structure is like this:
![ordered-table-in-windows8.jpg]()
We can build a pivot table with this query:
SELECT
ShipCountry AS Countries, [1996], [1997],
[1998]
FROM
(SELECT
OrderID, ShipCountry,
YEAR(OrderDate)
AS Years
FROM
Orders) ShipsTable
PIVOT
(
COUNT(OrderID)
FOR
Years IN
([1996], [1997],
[1998])
)
AS PivotTable
ORDER
BY ShipCountry
This is the output of the query within management studio:
![build-report-in-windows8.jpg]()
BUILD THE REPORT
Now we want to explain how to use pivot table within an Entity Model and LINQ
syntax, we use the same example above, so you can easily understand differences
between the two ways to operate.
The first thing that we must do is to create an empty ASP.NET project, now we
can add a default webform called "Default.aspx" and with only a simple panel
called "panelPivotTable"
in it:
<body>
<asp:Panel
ID="panelPivotTable"
runat="server">
</asp:Panel>
</body>
In this panel we'll show a table with the representation of the pivot table
data. The next thing is to add an Entity Data Model when we'll map the Orders
table of Northwind database:
![pivot-table-in-windows8.jpg]()
At this point we can write code in the code behind file
"Default.aspx.vb",
is useful pay particular attention to the part of the code in which we write the
SQL Pivot table with LINQ syntax:
Dim query = From
g In _
(From
o In db.Orders _
Group o By
o.ShipCountry Into
Group)
Let
m1996 As String
= (From r In
g.Group _
Where
r.ShippedDate.Value.Year = "1996" _
Select
r.ShipCountry).Count _
Let m1997 As
String = (From r
In g.Group _
Where
r.ShippedDate.Value.Year = "1997" _
Select
r.ShipCountry).Count _
Let m1998 As
String = (From r
In g.Group _
Where
r.ShippedDate.Value.Year = "1998" _
Select
r.ShipCountry).Count _
Select New
With { _
.Country = g.ShipCountry, _
.y1996 = m1996, _
.y1997 = m1997, _
.y1998 = m1998 _
}
What we are doing is to make a query grouped
by ShipCountry field, the keyword "Let" is an useful way to storing the results
of a query in a new variable initialized with the result of the expression, so
in our example we are storing in String variable
m1996
the result of query "(From
r In g.Group r.ShippedDate.Value.Year =
"1996" Select
r.ShipCountry).Count" and so on
for the others years.
Then we retrieve the valorized values in new
variables within Select New
statement, you can notice that the variables (ex.
.Country, .y1996 etc.)
have a dot before the name, this dot is needed when we create new customized
fields in a Select statement. Now that we know how to retrieve a pivot table
from our database we can build the report we can loop through data and write
values in a table.
For
Each item In
query
row =
New TableRow
cell =
New TableCell
cell.Text = item.Country
row.Cells.Add(cell)
...........
table.Rows.Add(row)
Next
panelPivotTable.Controls.Add(table)
And this is the result of our work:
![result-in-windows8.jpg]()
I hope that this tutorial will help you in
development of easy little reports as you can see in the attached source
project.