title: Dynamic PIVOT using C# Linq
body: <p>I am trying to use following code to create the PIVOT but its not working.</p>
  
  <p>It's giving me compile time error. I don't know linq so unable to use it.</p>
  
  <p>Please help :</p>
  
  <pre><code>   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(&lt;DataColumn&gt;)
          .Select( c =&gt; c.ColumnName )
          .ToArray();
  
      // prep results table
      DataTable result = temp.DefaultView.ToTable(true, pkColumnNames).Copy();
      result.PrimaryKey = result.Columns.Cast(&lt;DataColumn&gt;).ToArray();
      dt.AsEnumerable()
          .Select(r =&gt;; r[pivotColumn.ColumnName].ToString())
          .Distinct().ToList()
          .ForEach (c =&gt; 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 =&gt; 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></pre>
  
  <p>Code from : <a href="http://michaeljswart.com/2011/06/forget-about-pivot/" rel="nofollow">http://michaeljswart.com/2011/06/forget-about-pivot/</a></p>
  
  <p>Moreover it tried to use following code, it works well except for it is not giving total sum for Value Column</p>
  
  <pre><code>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&lt;string&gt; columnXValues = new List&lt;string&gt;();
  
  
              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) &amp;&amp; !string.IsNullOrEmpty(columnZ))
              {
                  //Read DISTINCT Values for Y Axis Column
                  List&lt;string&gt; columnYValues = new List&lt;string&gt;();
  
                  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);
  </code></pre>
  
  <p>Please help :)</p>