title: Formatting SQL Output (Pivot)
body: <p>This is running on SQL Server 2008.</p>
  
  <p>Anyway, I have sales data, and I can write a query to get the output to look like this:</p>
  
  <pre><code>id | Name       | Period  | Sales
  1  | Customer X | 2013/01 | 50
  1  | Customer X | 2013/02 | 45
  </code></pre>
  
  <p>etc. Currently, after running this data, I am rearranging the data in the code behind so that the final output looks like this:</p>
  
  <pre><code>id  | Name       | 2013/01 | 2013/02
  1   | Customer X |   50    |   40
  </code></pre>
  
  <p>The issues are:</p>
  
  <ol>
  <li>The date (YYYY/MM) range is an input from the user. </li>
  <li>If the user selects more outputs (like, say, address, and a ton of other possible fields relating to that customer), that information is duplicated in <em>every</em> line. When you're doing 10-15 items per line, over a period of 5+ years, for 50000+ users, this causes problems with running out of memory, and is also inefficient.</li>
  </ol>
  
  <p>I've considered pulling only the necessary data (the customer id -- how they're joined together, the period, and the sales figure), and then after the fact running a separate query to get the additional data. This doesn't seem like it would be efficient though, but it's a possibility.</p>
  
  <p>The other, which is what I'm thinking should be the best option, would be to rewrite my query to go ahead and do what my current code behind is doing, and pivot the data together, that way the customer data is never duplicated and I'm not moving a lot of unnecessary data around.</p>
  
  <p>To give a better example of what I'm working with, let's assume these tables:</p>
  
  <p>Address</p>
  
  <pre><code>id | HouseNum | Street | Unit | City | State
  </code></pre>
  
  <p>Customer</p>
  
  <pre><code>id | Name | 
  </code></pre>
  
  <p>Sales</p>
  
  <pre><code>id | Period | Sales
  </code></pre>
  
  <p>So I would like to join these tables on the customer id, display all of the address data, assume the user inputs "2012/01 -- 2012/12", I can translate that into 2012/01, 2012/02 ... 2012/12 in my code behind to input into the query before it executes, so I have that available.</p>
  
  <p>What I want it to look like would be:</p>
  
  <pre><code>id | Name | HouseNum | Street   | City | State | 2012/01 | 2012/02 | ... | 2012/12
  1  | X    | 100      | Main St. | ABC  | DEF   |   30    |         | ... |   20
  </code></pre>
  
  <p>(no sales data for that customer on 2012/02 -- if any of the data is blank I want it to be a blank string "", not a NULL)</p>
  
  <p>I realize I may not be explaining this the best way possible, so just let me know and I'll add more information. Thank you!</p>
  
  <p>edit: oh, one last thing. Would it be possible to add a Min, Max, Avg, &amp; Total columns to the end, which sum up all of the pivoted data? It wouldn't be a big deal to do it on the code behind, but the more sql server can do for me the better, imo!</p>
  
  <p>edit: One more, the period is in the tables as "2013/01" etc, but I'd like to rename them to "Jan 2013" etc, if it's not too complicated?</p>