Click here to Skip to main content
14,391,232 members

Transpose a DataTable using C#

Rate this:
4.60 (26 votes)
Please Sign up or sign in to vote.
4.60 (26 votes)
12 Feb 2010CPOL
Transpose DataTable rows into columns and vice versa 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

StatusPhase IPhase IIPhase III
Not Started100200300
Partially Completed101201301
Successfully Completed102202302
Blocked103203303
Completed with Conditions104204304
Cannot proceed105205305

Transposed Table

StatusNot StartedPartially CompletedSuccessfully CompletedBlockedCompleted with ConditionsCannot proceed
Phase I100101102103104105
Phase II200201202203204205
Phase III300301302303304305
<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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Satheesh Kumar Subramanian
Architect
India India
Solution Architect | Technical Lead | Team Lead | Expertise in Microsoft Technologies |

Comments and Discussions

 
QuestionGreat code! Pin
Leo Gurdian5-Aug-16 9:50
MemberLeo Gurdian5-Aug-16 9:50 
QuestionThank You ! Pin
MarcusCole683326-Jan-16 12:03
professionalMarcusCole683326-Jan-16 12:03 
SuggestionEasy Method to DataTable Transport Pin
Virendra Agrawal10-Aug-12 17:58
MemberVirendra Agrawal10-Aug-12 17:58 
GeneralMy vote of 5 Pin
m.varaprasadmca6-Jun-12 3:54
Memberm.varaprasadmca6-Jun-12 3:54 
GeneralMy vote of 5 Pin
Prince Antony G24-Jan-12 3:05
MemberPrince Antony G24-Jan-12 3:05 
GeneralMy vote of 5 Pin
TweakBird8-Feb-11 18:38
MemberTweakBird8-Feb-11 18:38 
GeneralMy vote of 5 Pin
franklinraj2-Dec-10 6:44
Memberfranklinraj2-Dec-10 6:44 
GeneralRe: My vote of 5 Pin
Satheesh Kumar Subramanian15-Dec-10 12:19
MemberSatheesh Kumar Subramanian15-Dec-10 12:19 
GeneralThanx Pin
David Cruwys29-Jul-10 16:02
MemberDavid Cruwys29-Jul-10 16:02 
GeneralVote of 3: Code is useful Pin
David Cruwys29-Jul-10 16:01
MemberDavid Cruwys29-Jul-10 16:01 
GeneralRe: Vote of 3: Code is useful Pin
Satheesh Kumar Subramanian2-Aug-10 5:31
MemberSatheesh Kumar Subramanian2-Aug-10 5:31 
General[My vote of 2] Works only with strings. Cannot work with any other datatype. Pin
Som Shekhar21-Nov-09 8:45
MemberSom Shekhar21-Nov-09 8:45 
GeneralMy vote of 1 Pin
Dave Kreskowiak21-Nov-09 7:16
mveDave Kreskowiak21-Nov-09 7:16 
Questionwouldn't this be better done IN the database? Pin
l a u r e n21-Nov-09 2:00
Memberl a u r e n21-Nov-09 2:00 
AnswerRe: wouldn't this be better done IN the database? Pin
Satheesh Kumar Subramanian21-Nov-09 2:28
MemberSatheesh Kumar Subramanian21-Nov-09 2:28 
GeneralCould do with more detail Pin
Richard MacCutchan21-Nov-09 1:15
mveRichard MacCutchan21-Nov-09 1:15 
GeneralRe: Could do with more detail Pin
Satheesh Kumar Subramanian21-Nov-09 2:24
MemberSatheesh Kumar Subramanian21-Nov-09 2:24 
GeneralRe: Could do with more detail Pin
Richard MacCutchan21-Nov-09 7:27
mveRichard MacCutchan21-Nov-09 7:27 
GeneralRe: Could do with more detail Pin
Member 1310034713-May-17 11:24
MemberMember 1310034713-May-17 11:24 
GeneralMy vote of 1 Pin
R. Giskard Reventlov21-Nov-09 0:41
MemberR. Giskard Reventlov21-Nov-09 0:41 
GeneralRe: My vote of 1 Pin
Satheesh Kumar Subramanian21-Nov-09 2:26
MemberSatheesh Kumar Subramanian21-Nov-09 2:26 

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.

Article
Posted 20 Nov 2009

Stats

147.2K views
21 bookmarked