title: Multi Line Chart
body: <p>I have been trying to make a multiline graph which pulls data from a database. 
  In an effort to do this i have written the following code to populate the chart:</p>
  
  <pre><code>    SqlConnection con1 = new SqlConnection();
      con1.ConnectionString = ConfigurationManager.ConnectionStrings["Removed"].ToString();
      SqlCommand cmd1 = new SqlCommand("SELECT YEAR(Start_Date) AS Year, Month(Start_Date) As Month, Cost, Utility_Type FROM import_Utilities WHERE YEAR(Start_Date) = 2011  AND Utility_Type = 'Water'");
      cmd1.Connection = con1;
      con1.Open();
  
      SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
      DataTable dt1 = new DataTable();
      da1.Fill(dt1);
      Chart1.DataSource = dt1;
  
      Chart1.Series["Series2"].XValueMember = "Month";
      Chart1.Series["Series2"].YValueMembers = "Cost";
      Chart1.Legends.Add(new Legend("Default") { Docking = Docking.Right });
  
  
      Chart1.DataBind();
      con1.Close();
  
      SqlConnection con = new SqlConnection();
      con.ConnectionString = ConfigurationManager.ConnectionStrings["Removed"].ToString();
      con.Open();
      SqlCommand cmd = new SqlCommand("SELECT YEAR(Start_Date) AS Year, Month(Start_Date) As Month, Cost, Utility_Type FROM import_Utilities WHERE YEAR(Start_Date) = 2012 AND Utility_Type = 'Water'");
      cmd.Connection = con;
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataTable dt = new DataTable();
      da.Fill(dt);
      Chart1.DataSource = dt;
      Chart1.Series["2010"].XValueMember = "Month";
      Chart1.Series["2010"].YValueMembers = "Cost";
  
      Chart1.DataBind();
      con.Close();
  </code></pre>
  
  <p>This code works except it has two data sources so only the bottom sqlconnection is populated to the chart.
  I am at lost of how to fix this, and i imagine their is a better way to go about doing this but i am at a loss.</p>
  
  <p>UPDATE:</p>
  
  <p>Using the example bellow i ended up using pivot sql to make a table (see picture). <img src="https://i.stack.imgur.com/oTDtW.jpg" alt="Pivot Table">
  Then i updated my code to be the following:</p>
  
  <pre><code> SqlCommand cmd = new SqlCommand("SELECT * FROM ( SELECT YEAR(Start_Date), CASE MONTH(Start_Date) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September'WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END as [Month], [Cost] FROM [HousingAccountingReports].[dbo].[import_Utilities] Where [Building] = 'Building B' AND [Utility_Type] = 'Electric') TableDate PIVOT (SUM([Cost]) FOR [Month] IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December] )) PivotTable");
      cmd.Connection = con;
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataTable dt = new DataTable();
      da.Fill(dt);
      Chart3.DataSource = dt;
      Chart3.Series["Series1"].XValueMember = "Month";
      Chart3.Series["Series1"].YValueMembers = "2010";
      Chart3.Series["Series2"].XValueMember = "Month";
      Chart3.Series["Series2"].YValueMembers = "2011";
      Chart3.Series["Series3"].XValueMember = "Month";
      Chart3.Series["Series3"].YValueMembers = "2012";
      Chart3.DataBind();`
  </code></pre>
  
  <p>When i run the page it errors to can't find column Month. And if i add:<br>
     dt.Columns.Add("Month");
          dt.Columns.Add("Year");`
  It can't find 2010, How do i get my chart piloting correctly; is their an issue with my pivot sql? </p>