Unpivot dataset using C#

Question

How to convert dataset columns into rows using C# as unpivot in SQL. I found a method to convert rows to columns . C# Rows to Columns . Any ideas to convert DataTable columns to rows ?? Thanks ..

My code as Ivan mentioned below

OracleDatabase database = null;               
DbCommand command = null;
 protected void Button1_Click(object sender, EventArgs e)
        {
            database = new OracleDatabase("Connection String");
            command = database.GetSqlStringCommand("select avg(PRMTN_PRDCT_GROUP_SK),avg(STORE_CLSTR_SK) from ALG_AVERAGE_BASELINE_SALE");
            DataSet ds = new DataSet();
            ds = database.ExecuteDataSet(command);               
            DataTable dt = new DataTable();
            dt = ds.Tables[0];
            dt.Columns.AddRange(new DataColumn[] { 
               new DataColumn("col1"), 
               new DataColumn("col2") });
            foreach (DataRow row in dt.Rows)
            {
                foreach (var column in row.ItemArray)
                    Response.Write(column);                    
            }
            Response.Write("\n");
            var result = dt.Columns
                          .Cast<DataColumn>()
                          .Select(column =>
                             dt.AsEnumerable()
                               .Select(row =>
                                  row.ItemArray[column.Ordinal].ToString()));
            Response.Write("\n");
            foreach (var row in result)
            {
                foreach (var column in row)
                    Response.Write(column);
                Response.Write("\n");
            }                  
        }

It works fine in console. But the rows are not unpivoted in my web application..

A `DataSet` has no columns, a `DataTable` has. So you want one table with one column(type `string`, name="ColumnName")? — Tim Schmelter, Jul 02 '12 at 11:09
Try http://stackoverflow.com/questions/10894660/lambda-expression-to-get-the-cells-vertically-for-a-section-containing-rows/10896150#10896150 — Ivan Golović, Jul 02 '12 at 11:12

surfmuggle · Answer 1 · 2022-06-19T18:58:40.997

Linqpad Demo for transposing (unpivoting) a DataTable

As far as i understood you would like to put the rows into columns. The sample below works for 3 rows (header + 2 data rows).

DataTable transposed

If you have more rows you must change the part dt2.Rows.Add(columnNames[i], dt.Rows[0].ItemArray[i],dt.Rows[1].ItemArray[i]); so that it matches the amount of rows. For lots of rows this soultion would have to be improved.

void Main() {
    var dt = GetDataTable("Sales");
    dt.Dump();
    UnpivotDataTable(dt).Dump();
}

public DataTable GetDataTable(string name) {
    var dt = new DataTable(name);   
    dt.Columns.Add("Id", typeof(string)); // dt.Columns.Add("Id", typeof(int)); 
    dt.Columns.Add("Apartement", typeof(string));   
    dt.Columns.Add("Monday", typeof(int));
    dt.Columns.Add("Tuesday", typeof(int));
    dt.Columns.Add("Wednesday", typeof(int));
    dt.Columns.Add("Thursday", typeof(int));
    dt.Columns.Add("Friday", typeof(int));      
    dt.Rows.Add(1, "Food", 300, 270, 310, 280, 500);
    dt.Rows.Add(2, "Electronics", 600, 470, 410, 380, 1500);    
    return dt;
}

public DataTable UnpivotDataTable(DataTable dt){
    string[] columnNames = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();           
    var dt2 = new DataTable("unpivot"); 
    dt2.Columns.Add("Headers", typeof(string)); 
    for(int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++){
        dt2.Columns.Add("Row" + rowIndex.ToString(), typeof(string)); 
    }
    
    for(int i=0; i < columnNames.Length; i++){
        // flaw: hardcoded is the amount of rows that are unpivoted
        dt2.Rows.Add(columnNames[i], dt.Rows[0].ItemArray[i],dt.Rows[1].ItemArray[i]);
    }
    return dt2;
}

That's transposing, not unpivoting data – Pawel Jun 19 '22 at 14:33 — Pawel, Jun 19 '22 at 14:33

score 1 · Answer 2 · answered May 03 '18 at 14:42

surfmuggle's answer is what I would call "transposing" the table (switching columns and rows). In excel, when you pivot data, you aggregate (sum/min/avg/etc) the values of one column (in his example, expenses or sales) by the attributes of another column (Apartement/department). You can further break down expenses by yet another column (day of week).

So when I want to "unpivot" data, I want to get back to that repetitive looking raw data so I can re-pivot it in some other way, usually by filtering it first so that I don't have so many columns in my new pivoted table.

This is what I want surfmuggle's data to turn into:

This is my C# code to unpivot his original data table into my desired data format:

public static DataTable UnpivotDataTable(DataTable pivoted)
{
    string[] columnNames = pivoted.Columns.Cast<DataColumn>()
        .Select(x => x.ColumnName)
        .ToArray();

    var unpivoted = new DataTable("unpivot");
    unpivoted.Columns.Add(pivoted.Columns[0].ColumnName, pivoted.Columns[0].DataType);
    unpivoted.Columns.Add("Attribute", typeof(string));
    unpivoted.Columns.Add("Value", typeof(string));

    for (int r = 0; r < pivoted.Rows.Count; r++)
    {
        for (int c = 1; c < columnNames.Length; c++)
        {
            var value = pivoted.Rows[r][c]?.ToString();
            if (!string.IsNullOrWhiteSpace(value))
            {
                unpivoted.Rows.Add(pivoted.Rows[r][0], columnNames[c], value);
            }
        }
    }

    return unpivoted;
}

score 0 · Answer 3 · answered Jul 02 '12 at 12:10

Try the following code to create a datatable then fill data in it.

  public DataTable getdatw()
{

    DataTable dtExcel = new DataTable();
    dtExcel.Columns.Add("Id", typeof(Int32));
    dtExcel.Columns.Add("Name", typeof(String));
    DataRow row;
    row = dtExcel.NewRow();
    row["Id"] = "1";
    row["Name"] = "kamal";
    dtExcel.Rows.Add(row);
    return dtExcel;
}

One thing more the data can be fill in number of ways it depends up on our requirement e.g, you want to fill from array,collection etc.

Unpivot dataset using C#

3 Answers3

Linqpad Demo for transposing (unpivoting) a DataTable