Telerik RadGrid can be bound to pivot table. This is especially useful when you want to display the data in a reverse order (vertical grid) but still keep the same presentation and structure in the source table.
The logic resides in the PivotTable method in the code-behind of the page which transforms the table rows into columns and the table columns into rows in the rendered grid.
 |
Note, however that features like paging, sorting, grouping, filtering, etc. will behave in a different way because the type of the data in the grid columns can vary (for example integers, strings, dates, etc.). This is due to the "vertical grid" effect. That is why sometimes they may return unexpected results and you should consider whether to enable them or execute your custom logic to manipulate the data in the grid. |
| C# |
 Copy Code |
|
public DataTable PivotTable(DataTable source)
{
DataTable dest = new DataTable("Pivoted" + source.TableName);
dest.Columns.Add(" ");
foreach (DataRow r in source.Rows)
dest.Columns.Add(r[0].ToString());
for (int i = 0; i < source.Columns.Count - 1; i++)
{
dest.Rows.Add(dest.NewRow());
}
for (int r = 0; r < dest.Rows.Count; r++)
{
for (int c = 0; c < dest.Columns.Count; c++)
{
if (c == 0)
dest.Rows[r][0] = source.Columns[r + 1].ColumnName;
else
dest.Rows[r][c] = source.Rows[c - 1][r + 1];
}
}
dest.AcceptChanges();
return dest;
}
protected void RadGrid1_NeedDataSource1(object source, GridNeedDataSourceEventArgs e)
{
RadGrid1.DataSource = PivotTable(GetDataTable("SELECT TOP 7 CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode, City, Country FROM Customers"));
}
public DataTable GetDataTable(String query)
{
OleDbConnection connection1 = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~/Grid/Data/Access/Nwind.mdb"));
OleDbDataAdapter adapter1 = new OleDbDataAdapter();
adapter1.SelectCommand = new OleDbCommand(query, connection1);
DataTable table1;
connection1.Open();
try
{
adapter1.Fill(table1);
}
finally
{
connection1.Close();
}
return table1;
} |
| VB.NET |
Copy Code |
|
Public Function PivotTable(ByVal [source] As DataTable) As DataTable
Dim dest As New DataTable("Pivoted" + [source].TableName)
dest.Columns.Add(" ")
Dim r As DataRow
For Each r In [source].Rows
dest.Columns.Add(r(0).ToString())
Next r
Dim i As Integer
For i = 0 To ([source].Columns.Count - 1) - 1
dest.Rows.Add(dest.NewRow())
Next i
For i = 0 To dest.Rows.Count - 1
Dim c As Integer
For c = 0 To dest.Columns.Count - 1
If c = 0 Then
dest.Rows(i)(0) = [source].Columns((i + 1)).ColumnName
Else
dest.Rows(i)(c) = [source].Rows((c - 1))((i + 1))
End If
Next c
Next i
dest.AcceptChanges()
Return dest
End Function
Public Function GetDataTable(ByVal query As String) As DataTable
Dim connection1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/Grid/Data/Access/Nwind.mdb"))
Dim adapter1 As New OleDbDataAdapter
adapter1.SelectCommand = New OleDbCommand(query, connection1)
Dim table1 As New DataTable
connection1.Open()
Try
adapter1.Fill(table1)
Finally
connection1.Close()
End Try
Return table1
End Function
Private Sub RadGrid1_NeedDataSource(ByVal source As Object, ByVal e As WebControls.GridNeedDataSourceEventArgs) Handles RadGrid1.NeedDataSource
RadGrid1.DataSource = PivotTable(GetDataTable("SELECT TOP 5 CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode FROM Customers"))
End Sub |