(untagged)

Display a SQL row vertically and column horizontally

Xun Ding

0.00/5 (No votes)

15 Aug 2003

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.

    <%@ 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"?>

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