Dynamic PIVOT using C# Linq

Question

I am trying to use following code to create the PIVOT but its not working.

It's giving me compile time error. I don't know linq so unable to use it.

Please help :

   DataTable Pivot(DataTable dt, DataColumn pivotColumn, DataColumn pivotValue) {
    // find primary key columns 
    //(i.e. everything but pivot column and pivot value)
    DataTable temp = dt.Copy();
    temp.Columns.Remove( pivotColumn.ColumnName );
    temp.Columns.Remove( pivotValue.ColumnName );
    string[] pkColumnNames = temp.Columns.Cast(<DataColumn>)
        .Select( c => c.ColumnName )
        .ToArray();

    // prep results table
    DataTable result = temp.DefaultView.ToTable(true, pkColumnNames).Copy();
    result.PrimaryKey = result.Columns.Cast(<DataColumn>).ToArray();
    dt.AsEnumerable()
        .Select(r =>; r[pivotColumn.ColumnName].ToString())
        .Distinct().ToList()
        .ForEach (c => result.Columns.Add(c, pivotColumn.DataType));

    // load it
    foreach( DataRow row in dt.Rows ) {
        // find row to update
        DataRow aggRow = result.Rows.Find(
            pkColumnNames
                .Select( c => row[c] )
                .ToArray() );
        // the aggregate used here is LATEST 
        // adjust the next line if you want (SUM, MAX, etc...)
        aggRow[row[pivotColumn.ColumnName].ToString()] = row[pivotValue.ColumnName];
    }

    return result;
}

Code from : http://michaeljswart.com/2011/06/forget-about-pivot/

Moreover it tried to use following code, it works well except for it is not giving total sum for Value Column

public DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues)
        {
            //Create a DataTable to Return
            DataTable returnTable = new DataTable();

            DataTable tempTable = table.Clone();

            if (string.IsNullOrEmpty(columnX))
            {
                columnX = table.Columns[0].ColumnName;
            }

            tempTable.Columns.Remove(columnX);


            //Add a Column at the beginning of the table
            //returnTable.Columns.Add(columnY);

            returnTable = tempTable.Clone();

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


            foreach (DataRow dr in table.Rows)
            {
                string columnXTemp = dr[columnX].ToString();
                if (!columnXValues.Contains(columnXTemp))
                {
                    //Read each row value, if it's 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);
                }
            }

            //Verify if Y and Z Axis columns re provided
            if (!string.IsNullOrEmpty(columnY) && !string.IsNullOrEmpty(columnZ))
            {
                //Read DISTINCT Values for Y Axis Column
                List<string> columnYValues = new List<string>();

                foreach (DataRow dr in table.Rows)
                {
                    if (!columnYValues.Contains(dr[columnY].ToString()))
                    {
                        columnYValues.Add(dr[columnY].ToString());
                    }
                }

                //Loop all Column Y Distinct Value
                foreach (string columnYValue in columnYValues)
                {
                    //Creates a new Row
                    DataRow drReturn = returnTable.NewRow();
                    drReturn[0] = columnYValue;
                    //foreach column Y value, The rows are selected distincted
                    DataRow[] rows = table.Select((columnY + "='") + columnYValue + "'");

                    //Read each row to fill the DataTable
                    foreach (DataRow dr in rows)
                    {
                        string rowColumnTitle = dr[columnX].ToString();

                        //Read each column to fill the DataTable
                        foreach (DataColumn dc in returnTable.Columns)
                        {
                            if (dc.ColumnName == rowColumnTitle)
                            {
                                //If Sum of Values is True it try to perform a Sum
                                //If sum is not possible due to value types, the value displayed is the last one read
                                if (sumValues)
                                {
                                    try
                                    {
                                        drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]);
                                    }
                                    catch
                                    {
                                        drReturn[rowColumnTitle] = dr[columnZ];
                                    }
                                }
                                else
                                {
                                    drReturn[rowColumnTitle] = dr[columnZ];

                                }
                            }
                        }
                    }

                    returnTable.Rows.Add(drReturn);

                }
            }
            else
            {
                throw new Exception("The columns to perform inversion are not provided");
            }

            //if a nullValue is provided, fill the datable with it
            if (!string.IsNullOrEmpty(nullValue))
            {
                foreach (DataRow dr in returnTable.Rows)
                {
                    foreach (DataColumn dc in returnTable.Columns)
                    {
                        if (string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
                        {
                            dr[dc.ColumnName] = nullValue;
                        }
                    }
                }
            }

            return returnTable;
        }

GetInversedDataTable(dtNormal, "Dated", "OrderStatus", "Qty", " ", true);

Please help :)

score 6 · Accepted Answer · answered Oct 13 '12 at 03:37

Here is the code with the compilation errors corrected:

DataTable Pivot(DataTable dt, DataColumn pivotColumn, DataColumn pivotValue) {
    // find primary key columns 
    //(i.e. everything but pivot column and pivot value)
    DataTable temp = dt.Copy();
    temp.Columns.Remove( pivotColumn.ColumnName );
    temp.Columns.Remove( pivotValue.ColumnName );
    string[] pkColumnNames = temp.Columns.Cast<DataColumn>()
        .Select( c => c.ColumnName )
        .ToArray();

    // prep results table
    DataTable result = temp.DefaultView.ToTable(true, pkColumnNames).Copy();
    result.PrimaryKey = result.Columns.Cast<DataColumn>().ToArray();
    dt.AsEnumerable()
        .Select(r => r[pivotColumn.ColumnName].ToString())
        .Distinct().ToList()
        .ForEach (c => result.Columns.Add(c, pivotColumn.DataType));

    // load it
    foreach( DataRow row in dt.Rows ) {
        // find row to update
        DataRow aggRow = result.Rows.Find(
            pkColumnNames
                .Select( c => row[c] )
                .ToArray() );
        // the aggregate used here is LATEST 
        // adjust the next line if you want (SUM, MAX, etc...)
        aggRow[row[pivotColumn.ColumnName].ToString()] = row[pivotValue.ColumnName];
    }

    return result;
}

I changed Cast(<DataColumn>) to Cast<DataColumn>() in two locations and got rid of the semicolon in the middle of a lambda expression. The second part of your question is a little trickier. You may want to ask it as its own question.

@RiskyMartin Just found this question... I fixed the code on my blog. — Michael J Swart, Dec 05 '13 at 04:34
@RiskyMartin, does this code actually work I I tried this and get errors on the following `temp.Columns.Remove( pivotValue.ColumnName );` for example it works for the most part but not what and or how I would have expected.. for example if I want to have a `DataTime` which has 31 days for example be the column names.. it adds them to the end of the existing datatable.I will try this code and see if it's been updated from `Michael J Stewart` blog being this is where the code came from — MethodMan, Feb 16 '16 at 16:47
@MethodMan I only fixed the compilation errors, so there could still be semantic errors. You might want to ask a separate question for this. And if you find a fix, please inform me (so I can update my answer) and the blog writer. — Risky Martin, Mar 31 '16 at 23:02

score 1 · Answer 2 · edited Jan 15 '14 at 09:34

1

Good one., but you might want to replace the below line

 .ForEach (c => result.Columns.Add(c, pivotColumn.DataType));

with this (change pivotColumn to pivotValue)

 .ForEach (c => result.Columns.Add(c, pivotValue.DataType));

Works perfectly for my requirement.

edited Jan 15 '14 at 09:34

CrazyCasta

26,917
4
45
72

answered Jan 15 '14 at 09:13

Raghu

699
7
14

3

While this code may answer the question, it would be better to include some context, explain how it works, and describe when to use it. Code-only answers are not useful in the long run. – ryanyuyu Aug 26 '15 at 18:54
1

isn't there a chance that pivotValue.DataType can return DBNull – Surya Pratap Nov 08 '18 at 05:44

Dynamic PIVOT using C# Linq

2 Answers2

Linked