Unable to pivot C# datatable based on two columns

Question

I am having a C# datatable with three columns. This is how the table looks:

DataTable dt = new DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Val", typeof(string));


             dt.Rows.Add("One", "NZ", "100");
        dt.Rows.Add("One", "EN", "200");
        dt.Rows.Add("One", "CA", "300");
        dt.Rows.Add("Two", "ZM", "400");
        dt.Rows.Add("Two", "SA", "200");
        dt.Rows.Add("Two", "NZ", "440");
        dt.Rows.Add("Two", "EN", "990");
        dt.Rows.Add("Three", "IN", "660");
        dt.Rows.Add("Three", "CH", "994");
        dt.Rows.Add("Three", "JP", "900");
        dt.Rows.Add("Three", "CA", "50");
        dt.Rows.Add("Four", "WI", "330");

Expected output after pivoting:

           NZ     EN    CA       ZM      SA       IN       CH     JP      WI
One        100    200   300
Two        440    990            400     200
Three                   50                        660       994    900 
Four                                                                       330

I want to pivot Type column but based on Name column.

I am trying to pivot it using below code and getting error:

public static DataTable PivotDataTable(DataTable table, string columnX,
                                                    params string[] columnsToIgnore)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            if (columnX == "")
                columnX = table.Columns[0].ColumnName;

            //Add a Column at the beginning of the table

            returnTable.Columns.Add(columnX);

            //Read all DISTINCT values from columnX Column in the provided DataTale
            List<string> columnXValues = new List<string>();

            //Creates list of columns to ignore
            List<string> listColumnsToIgnore = new List<string>();
            if (columnsToIgnore.Length > 0)
                listColumnsToIgnore.AddRange(columnsToIgnore);

            if (!listColumnsToIgnore.Contains(columnX))
                listColumnsToIgnore.Add(columnX);

            foreach (DataRow dr in table.Rows)
            {
                string columnXTemp = dr[columnX].ToString();
                //Verify if the value was already listed
                if (!columnXValues.Contains(columnXTemp))
                {
                    //if the value id different from others provided, add to the list of 
                    //values and creates a new Column with its value.
                    columnXValues.Add(columnXTemp);
                    returnTable.Columns.Add(columnXTemp);
                }
                else
                {
                    //Throw exception for a repeated value
                    throw new Exception("The inversion used must have " +
                                        "unique values for column " + columnX);
                }
            }

            //Add a line for each column of the DataTable

            foreach (DataColumn dc in table.Columns)
            {
                if (!columnXValues.Contains(dc.ColumnName) &&
                    !listColumnsToIgnore.Contains(dc.ColumnName))
                {
                    DataRow dr = returnTable.NewRow();
                    dr[0] = dc.ColumnName;
                    returnTable.Rows.Add(dr);
                }
            }

            //Complete the datatable with the values
            for (int i = 0; i < returnTable.Rows.Count; i++)
            {
                for (int j = 1; j < returnTable.Columns.Count; j++)
                {
                    returnTable.Rows[i][j] =
                      table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();
                }
            }

            return returnTable;
        }

Error I am getting: 'The inversion used must have unique values for column Type'

How can I fix the error?

Answer 1

Using this extension method:

// pivot a DataTable to a new DataTable
// By field is row id and grouping key
// Over field creates new column names
// Value field is value for new columns
public static DataTable PivotByOverWith(this DataTable dt, string ByRowFieldName, string OverColFieldName, string WithValueFieldName) {
    var res = new DataTable();
    if (dt.Rows.Count > 0) {
        var dtg = dt.AsEnumerable().GroupBy(r => r[ByRowFieldName], r => new { Over = r[OverColFieldName].ToString(), With = r[WithValueFieldName] });

        var hasByRow = ByRowFieldName != OverColFieldName;
        if (hasByRow)
            res.Columns.Add(new DataColumn(ByRowFieldName, dt.Columns[ByRowFieldName].DataType));

        var valueDataType = dt.Columns[WithValueFieldName].DataType;
        var colNames = dtg.SelectMany(rg => rg.Select(r => r.Over)).Distinct();
        foreach (var n in colNames)
            res.Columns.Add(n, valueDataType);

        foreach (var rg in dtg) {
            var newr = res.NewRow();
            if (hasByRow)
                newr[ByRowFieldName] = rg.Key;
            foreach (var r in rg)
                newr[r.Over] = r.With;
            res.Rows.Add(newr);
        }
    }
    return res;
}

You can compute your answer with:

var ans = dt.PivotByOverWith("Name", "Type", "Val");

Answer 2

This is how I have done it hundreds of times before :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Val", typeof(int));


            dt.Rows.Add("One", "NZ", 100);
            dt.Rows.Add("One", "EN", 200);
            dt.Rows.Add("One", "CA", 300);
            dt.Rows.Add("Two", "ZM", 400);
            dt.Rows.Add("Two", "SA", 200);
            dt.Rows.Add("Two", "NZ", 440);
            dt.Rows.Add("Two", "EN", 990);
            dt.Rows.Add("Three", "IN", 660);
            dt.Rows.Add("Three", "CH", 994);
            dt.Rows.Add("Three", "JP", 900);
            dt.Rows.Add("Three", "CA", 50);
            dt.Rows.Add("Four", "WI", 330);

            string[] types = dt.AsEnumerable().Select(x => x.Field<string>("Type")).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Name");
            foreach (string type in types)
            {
                pivot.Columns.Add(type, typeof(int));
            }

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("Name"));

            foreach (var group in groups)
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Name"] = group.Key;
                foreach (DataRow row in group)
                {
                    newRow[row.Field<string>("Type")] = row.Field<int>("Val");
                }
            }

        }
    }
}