title: Using Entity Framework or GridView to pivot data in C# ASP.Net?
body: <p>I essentially have this dataset:
  </p>
  
  <pre><code>MyDbContext db = new MyDbContext();
  GridView gridView = new GridView();
  gridView.DataSource = db.Services;
  gridView.DataBind();
  </code></pre>
  
  <p>Which gives me this table:
  </p>
  
  <pre class="lang-none prettyprint-override"><code>+-----------+-----------+---------+---------+
  |   Name    |  Machine  | Status  | Version |
  +-----------+-----------+---------+---------+
  | Service 1 | Machine 1 | Running |   2.2.2 |
  | Service 1 | Machine 2 | Running |   2.2.2 |
  | Service 1 | Machine 3 | Running |   2.2.2 |
  | Service 2 | Machine 1 | Running |   2.2.2 |
  | Service 2 | Machine 2 | Running |   2.2.2 |
  | Service 2 | Machine 3 | Running |   2.2.2 |
  | Service 3 | Machine 1 | Running |   2.2.2 |
  | Service 3 | Machine 2 | Running |   2.2.2 |
  | Service 3 | Machine 3 | Running |   2.2.2 |
  +-----------+-----------+---------+---------+
  </code></pre>
  
  <p>I would now like to "pivot" the name column to get a table like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>+-----------+-----------+-----------+-----------+
  |  Machine  | Service 1 | Service 2 | Service 3 |
  +-----------+-----------+-----------+-----------+
  | Machine 1 | Running   | Running   | Running   |
  | Machine 2 | Running   | Running   | Running   |
  | Machine 3 | Running   | Running   | Running   |
  +-----------+-----------+-----------+-----------+
  </code></pre>
  
  <p>I am simply wondering if Entity Framework or GridView have any good tools to do this. I have been trying to find a solution on google, but short of creating my own pivot function I haven't found any useful solutions.</p>
  
  <p><strong>Edit 2:</strong></p>
  
  <p><strong>Notice:</strong> This is a slimmed down version of my actual environment. I have more than 3 machines to monitor and the ability to dynamically add more machines. I do only have 3 services at the moment, but this will probably expand later with <code>Service 4</code> and <code>Service 5</code> on existing or new machines. So I need a general solution for the pivoting. Thanks!</p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>I have tried to use Entity Framework to create a view by using PIVOT:
  </p>
  
  <pre class="lang-cs prettyprint-override"><code>db.Database.ExecuteSqlCommand(
  "CREATE VIEW pivotedServices AS (" +
  "SELECT " +
      "Machine, [Service 1], [Service 2], [Service 3] " +
  "FROM " +
      "(dbo.Services) " +
  "PIVOT" +
  "( Status FOR [Name] IN ( [Service 1], [Service 2], [Service 3])) AS pvt);"
  );
  </code></pre>
  
  <p>But didn't get it to work. Kept getting error "near '('.", after some testing it seems to be the first parenthesis before the <code>SELECT</code> statement that is the issue. Removing the surrounding parenthesis gives error "near SELECT.". Thus, I gave up on this idea.</p>
  
  <p>I also have tried using a couple of for-loops to "pivot" manually, I got it working fairly well. But I feel like there should be a better solution. If there isn't, fine I guess, but I'm hoping you guys have one.</p>