Transpose a datatable

Question

I have the data table below. I would like to change the data table to transform the data table into the one next to it. How can I do this?

The reason why i would like o do this is because the excel file brings the data in like shown and then i would like to sort this data out on Row 1

Current table

      || Col 1 || Col 2 || Col 3
____________________________________    
Row 1 || 10    ||  20   || 30
Row 2 || 1     ||  2    || 3

New Datatable

      || Row 1 || Row 2 
Col 1 ||  10   ||   1
Col 2 ||  20   ||   2
Col 3 ||  30   ||   3

Welcome to Stack Overflow! Please show us what you've tried. — Corey Adler, Feb 11 '13 at 17:06
They want me to sort the columns by row 1 from highest to lowets but cant finda mathod of doing this — user2061088, Feb 11 '13 at 17:08
http://stackoverflow.com/questions/12838403/c-sharp-transpose-method-to-transpose-rows-and-columns-in-excel-sheet, http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C — poke, Feb 11 '13 at 17:14

score 11 · Accepted Answer · answered Feb 11 '13 at 17:15

.NET does not include a method to transpose data tables. You have to make your own. This website has a tutorial on an example transpose method. I will copy and paste the code snippet below:

private DataTable Transpose(DataTable dt)
{
    DataTable dtNew = new DataTable();

    //adding columns    
    for(int i=0; i<=dt.Rows.Count; i++)
    {
       dtNew.Columns.Add(i.ToString());
    }



    //Changing Column Captions: 
    dtNew.Columns[0].ColumnName = " ";

     for(int i=0; i<dt.Rows.Count; i++) 
     {
      //For dateTime columns use like below
       dtNew.Columns[i+1].ColumnName = Convert.ToDateTime(dt.Rows[i].ItemArray[0].ToString()).ToString("MM/dd/yyyy");
      //Else just assign the ItermArry[0] to the columnName prooperty
     }

    //Adding Row Data
    for(int k=1; k<dt.Columns.Count; k++)
    {
        DataRow r = dtNew.NewRow();
        r[0] = dt.Columns[k].ToString(); 
        for(int j=1; j<=dt.Rows.Count; j++)
        r[j] = dt.Rows[j-1][k];  
        dtNew.Rows.Add(r);
    }

 return dtNew;
}

there is no date time coloumn all are strings – user2061088 Feb 11 '13 at 17:22 — user2061088, Feb 11 '13 at 17:22

score 0 · Answer 2 · answered Feb 11 '13 at 17:14

DataTable newDt = new DataTable();
//Select indexes
var indexes = dt.Rows.Cast<DataRow>().Select(row => dt.Rows.IndexOf(row));
//Select the columns
var newCols = indexes.Select(i => "Row" + i);
//Add columns
foreach(var newCol in newCols)
{
  newDt.Add(newCol);
}
//Select rows
var newRows = dt.Rows.Cast<DataColumn>().Select(col =>
                                                  {
                                                    row = newDt.NewRow();
                                                    foreach(int i in indexes)
                                                    {
                                                      row[i] = dt.Rows[i][col.Name];
                                                    }
                                                    return row;
                                                  });
//Add row to new datatable
foreach(var row in newRows)
{
  newDt.Add(row);
}

Did you test the above code? DataTable has no Add method off the base object, as attempted in your foreach loops. — Jazimov, Jul 28 '20 at 20:42

score -3 · Answer 3 · answered Feb 11 '13 at 17:13

Create a new table, with reversed dimensions from what you previously had, and assign the previous columns to the new rows, and the old rows to the new columns.

eg:

oldCol1 -> newRow1
oldCol2 -> newRow2
oldCol3 -> newRow3
oldCol4 -> newRow4
oldRow1 -> newCol1
oldCol2 -> newCol2
oldvar[a][b] -> newvar[b][a]

and the list goes on, assumming that you are going from

col1 || col2 || col3 || col4
row1 ||[a][b]||[c][d]||[e][f]
row2 ||[g][h]||[i][j]||[k][l]

to

row1 || col1 || col2
row2 ||[a][b]||[g][h]
row3 ||[c][d]||[i][j]
row4 ||[e][f]||[k][l]

If you can set up a table, you can assign enw and old variables. Just use the old variables in the new spot when creating the table. I don't know C#, but I've done this in `BASIC`, `java` and a modified version of `c` — Azulflame, Feb 11 '13 at 17:19

Transpose a datatable

3 Answers3

Linked