Click here to Skip to main content
12,065,756 members (26,788 online)
Click here to Skip to main content
Add your own
alternative version

Stats

165.5K views
1.3K downloads
62 bookmarked
Posted

Display a SQL row vertically and column horizontally

, 15 Aug 2003
Rate this:
Please Sign up or sign in to vote.
This article demonstrates how to flip and display rows and columns of a DataSet table.

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

Share

About the Author

Xun Ding
United States United States
A web developer who swims in the stream of ideas, fight it, enjoy it, and thrive. Hopefully!

You may also be interested in...

Comments and Discussions

 
QuestionC# code available? Pin
sanme9812-May-04 19:22
membersanme9812-May-04 19:22 
AnswerRe: C# code available? Pin
Xun Ding13-May-04 11:35
memberXun Ding13-May-04 11:35 
GeneralRe: C# code available? Pin
sanme9813-May-04 17:59
membersanme9813-May-04 17:59 
GeneralRe: C# code available? Pin
Xun Ding13-May-04 18:17
memberXun Ding13-May-04 18:17 
AnswerRe: C# code available? Pin
Xun Ding14-May-04 13:01
memberXun Ding14-May-04 13:01 
GeneralRe: C# code available? Pin
sanme9816-May-04 17:28
membersanme9816-May-04 17:28 
GeneralRe: C# code available? Pin
Navvy17-Jun-04 14:32
memberNavvy17-Jun-04 14:32 
GeneralRe: C# code available? Pin
Xun Ding18-Jun-04 12:37
memberXun Ding18-Jun-04 12:37 
AnswerC# code available! Pin
_Groker4-May-07 6:18
member_Groker4-May-07 6:18 
GeneralGreat Work!! Pin
bonniew24-Feb-04 5:17
memberbonniew24-Feb-04 5:17 
QuestionXSL ? Pin
Sébastien Lorion16-Aug-03 14:21
memberSébastien Lorion16-Aug-03 14:21 
AnswerRe: XSL ? Pin
curry16-Aug-03 18:13
membercurry16-Aug-03 18:13 
GeneralRe: XSL ? Pin
Sébastien Lorion16-Aug-03 20:34
memberSébastien Lorion16-Aug-03 20:34 
GeneralRe: XSL ? Pin
curry17-Aug-03 12:11
membercurry17-Aug-03 12:11 
GeneralRe: XSL ? Pin
Sébastien Lorion17-Aug-03 12:57
memberSébastien Lorion17-Aug-03 12:57 
QuestionFix ColumnNames? Pin
spieler12-Aug-03 22:26
memberspieler12-Aug-03 22:26 
AnswerRe: Fix ColumnNames? Pin
Anonymous13-Aug-03 5:07
sussAnonymous13-Aug-03 5:07 
AnswerRe: Fix ColumnNames? Pin
curry13-Aug-03 9:15
membercurry13-Aug-03 9:15 
GeneralWindows Forms Pin
Anonymous12-Aug-03 7:38
sussAnonymous12-Aug-03 7:38 
GeneralExecellent coder Pin
Anonymous11-Aug-03 18:59
sussAnonymous11-Aug-03 18:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160207.1 | Last Updated 16 Aug 2003
Article Copyright 2003 by Xun Ding
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid