Display a SQL row vertically and column horizontally
3.33/5 (17 votes)
Aug 11, 2003
1 min read
203068
1382
This article demonstrates how to flip and display rows and columns of a DataSet table.
Introduction
For displaying data in a table, DataGrid probably is the best choice. However, sometimes you may find it necessary to render your table in a different manner. Say, when you have a database table that has more than twenty fields and only a couple of records, I bet you would want to display all the rows vertically and the columns horizontally. Actually a week ago I stumbled into this problem myself. Checking online, I found some users had the same problem, yet there is not a workable answer ready for sharing. Therefore I present my solution below. It may not be the best answer, but it certainly is quick and simple to me.
The code
Solution 1
For saving the trouble of running SQL scripts to recreate a table, I created a .xml file for use. But the code works perfectly with any database table too.
Briefly speaking, the code first reads a .xml file into a DataSet, then saves all data into a two dimensional array. The size of array is dynamically decided by the size of the DataSet table. After the array is populated with data, we build up a table programmatically by adding TableRows and TableCells.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.XML" %>
<html>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim dataArray(,) as string
dim i, j as integer
'Create instance of DataSet object
dim DS as DataSet = new DataSet()
DS.ReadXml(Server.MapPath("Employees.xml"))
dim rowCount as integer= DS.Tables(0).Rows.Count
dim colCount as integer= DS.Tables(0).Columns.Count
redim dataArray(rowCount, colCount)
'save data in a two dimensional array
for i = 0 to rowCount -1
for j = 0 to colCount -1
if DS.Tables(0).Rows(i).item(j) is DBNull.value
dataArray(i,j)=""
else
dataArray(i,j) =DS.Tables(0).Rows(i).item(j)
end if
next
next
'Switch columns and rows, dynamically filling in the table
dim r as TableRow
dim c as TableCell
for j = 0 to colCount -1
r = new TableRow()
c = new TableCell()
c.Controls.Add(new _
LiteralControl(DS.Tables(0).Columns(j).ColumnName))
c.VerticalAlign = VerticalAlign.Top
c.Style("background-color") = "lightblue"
r.cells.add(c)
for i = 0 to rowCount - 1
c = new TableCell()
c.Controls.Add(new LiteralControl(dataArray(i,j)))
c.VerticalAlign = VerticalAlign.Top
r.cells.add(c)
next i
Table1.Rows.Add(r)
next j
End Sub
</script>
<center>
<h3> Display Employee Table Vertically</h3>
</center>
<body topmargin="0" leftmargin="0" marginwidth="0" marginheight="0">
<form runat="server" ID="Form1">
<asp:Table id="Table1" Font-Name="Verdana" Font-Size="8pt"
CellPadding=3 CellSpacing=0 BorderColor="black"
BorderWidth="1" Gridlines="Both" runat="server"/>
</form>
</body>
</html>
Solution 2
This solution, using XSL to transform XML into HTML in a format desired, is based on a user's suggestion (Thanks). The following is part of the hard-coded XSL file.
<TABLE border="1">
<COLGROUP WIDTH="100" ALIGN="CENTER" bgcolor="lavender"></COLGROUP>
<TR><TD>EmployeeID</TD>
<xsl:for-each select="NewDataSet/Table">
<TD><xsl:value-of select="EmployeeID"/></TD>
</xsl:for-each>
</TR>
<TR><TD>LastName</TD>
<xsl:for-each select="NewDataSet/Table">
<TD><xsl:value-of select="LastName"/></TD>
</xsl:for-each>
</TR>
...
To use the XSL file, we need to add XSL reference to the original XML file:
<?xml-stylesheet type="text/xsl" href="Employees.xsl"?>