Display a SQL row vertically and column horizontally

Xun Ding

Rate me:

3.33/5 (17 votes)

15 Aug 20031 min read

201.2K

1.4K

This article demonstrates how to flip and display rows and columns of a DataSet table.

Download source files - 3.03 Kb

Sample screenshot

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.

ASP.NET

<%@ 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.

HTML

 <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

<?xml-stylesheet type="text/xsl" href="Employees.xsl"?>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Xun Ding

United States