Transpose a DataTable using C#

Introduction

This article helps to transpose (convert rows into columns and columns into rows) a DataTable using C# code in an ASP.NET Web application (can be used in Windows Form as well).

Background

This articles uses .NET Framework 3.5, can be used in any version as DataTable is applicable to all. Readers should be familiar with basic ASP.NET, C# & DataTable concepts to understand this article.

Using the Code

This code uses a DataTable with four columns and three rows as shown below. After calling the method GenerateTransposedTable, it will give you the output which is shown in the second table.

This can be used in the DataGrid control (both in Windows and Web applications). Using a single click, we can transpose a table. This may help us in most of the reporting functionalities.

Here, I have used it in an ASP.NET web page.

Actual Table

Status	Phase I	Phase II	Phase III
Not Started	100	200	300
Partially Completed	101	201	301
Successfully Completed	102	202	302
Blocked	103	203	303
Completed with Conditions	104	204	304
Cannot proceed	105	205	305

Transposed Table

Status	Not Started	Partially Completed	Successfully Completed	Blocked	Completed with Conditions	Cannot proceed
Phase I	100	101	102	103	104	105
Phase II	200	201	202	203	204	205
Phase III	300	301	302	303	304	305

<table id="TableTopGrid" 
        style="width: 100%; border-left: darkgray 1px solid;"
        cellspacing="0" cellpadding="0" align="center">                           
    <tr>
    <td style="width:100%; background-color: #66ccff" align="center">
        <asp:Button ID="btnTransposeReport" 
            runat="server" Font-Bold="True" 
            Font-Names="Tahoma"
            Font-Size="8pt" Text="Query" 
            Width="75px" ForeColor="Navy" 
            OnClick="btnTransposeReport_Click" />
    </td>
    </tr>
    <tr>
    <td width="100%" align="center" valign="top">
        <asp:DataGrid ID="GridReport" runat="server"
           Height="100%" Width="100%" HorizontalAlign="Center"
           Font-Size="8pt" EditItemStyle-Wrap="true" 
           Font-Names="Tahoma" PageSize="50"
           AutoGenerateColumns="True" 
           AllowPaging="False" AllowSorting="False" GridLines="Both"
           ShowFooter="False" BackColor="AliceBlue">
        <ItemStyle Height="18px" Width="100%" 
            HorizontalAlign="Center" CssClass="Grid" />
        <HeaderStyle Font-Size="8pt" Font-Names="Tahoma" 
            Font-Bold="False" Width="100%" Height="20px"
            ForeColor="White" BackColor="DimGray" 
            HorizontalAlign="Center" CssClass="Grid" />

        </asp:DataGrid>
       </td>
    </tr>
    <tr>
    <td style="width:100%; background-color:DimGray" align="center">
        &nbsp;
    </td>
    </tr>
</table>

Here is the code:

protected void Page_Load(object sender, EventArgs e)
{        
     if (!IsPostBack)
     {
         GridReport.DataSource = <your datatable>;
         // This is the table I shown in Figure 1.1

         GridReport.DataBind();

         // Your other codes here (if any)
     }
}

protected void btnTransposeReport_Click(object sender, EventArgs e)
{
     DataTable inputTable = <your datatable>;
     // Table shown in Figure 1.1

     DataTable transposedTable = GenerateTransposedTable(inputTable);

     GridReport.DataSource = transposedTable;
     // Table shown in Figure 1.2

     GridReport.DataBind();
}

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();

     // Add columns by looping rows

     // Header row's first column is same as in inputTable
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns        
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}

Conclusion

You can notice that the actual report is transposed (rows into columns and columns into rows) as shown in the figure.

History

• 21^st November, 2009: Initial post
• 12^th February, 2010: Updated article - added Background section