title: SQL Pivot Table producing duplicates
body: <p>Developers,</p>
  
  <p>I am new to pivot tables, and am having a little problem with duplicates.  My table, before pivoting looks like so:</p>
  
  <p><strong>location</strong> | <strong>food</strong><br>
  Tennessee | pear<br>
  Tennessee | orange<br>
  Florida | orange<br>
  Florida | apple<br>
  Virginia | pear</p>
  
  <p>Here is the code to pivot, which works fine:</p>
  
  <pre><code>SELECT PivotTable.location, [apple], [orange], [pear]
  FROM
  (SELECT location, food FROM someTable) as inventory
  PIVOT
  (COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable
  </code></pre>
  
  <p>This produces an output like so:</p>
  
  <p><strong>Location</strong> | <strong>Apple</strong> | <strong>Orange</strong> | <strong>Pear</strong><br>
  Tennessee | 0 | 1 | 1<br>
  Florida | 1 | 1 | 0<br>
  Virginia | 0 | 0 | 1</p>
  
  <p>Which as I said works fine.  However, I added new columns for comments to my original table, like so:</p>
  
  <p><strong>location</strong> | <strong>food</strong> | <strong>apple_comments</strong> | <strong>orange_comments</strong> | <strong>pear_comments</strong></p>
  
  <p>Tennessee | pear | NULL | NULL | NULL<br>
  Tennessee | orange | NULL | very juicy | NULL <br>
  Florida | orange | NULL | NULL | NULL <br>
  Florida | apple | crisp | NULL | NULL <br>
  Virginia | pear | NULL | NULL| tasty <br></p>
  
  <p>Here is my altered pivot table to account for the comments:</p>
  
  <pre><code>SELECT PivotTable.location, [apple], [apple_comments], [orange], [orange_comments], [pear], [pear_comments]
  FROM
  (SELECT location, food, apple_comments, orange_comments, pear_comments FROM someTable) as inventory
  PIVOT
  (COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable
  </code></pre>
  
  <p>This produces an output like so:</p>
  
  <p><strong>Location</strong> | <strong>Apple</strong> | <strong>apple_comments</strong> | <strong>Orange</strong> | <strong>Orange_comments</strong> | <strong>Pear</strong> | <strong>Pear_comments</strong><br>
  Tennessee | 0 | NULL | 0 | NULL | 1 | NULL<br>
  Tennessee | 0 | NULL | 1 | very juicy | 0 | NULL<br>
  Florida | 0 | NULL | 1 | NULL | 0 | NULL<br>
  Florida | 1 | crisp | 1 | NULL | 0 | NULL<br>
  Virginia | 0 | NULL | 1 | NULL | 1 | tasty</p>
  
  <p>So, essentially, it is creating a duplicate row when comments are added for each entry where there are multiple locations.  In the case of Virginia, there is only one entry, so the row turns out fine.</p>
  
  <p>It almost seems like I need to do another pivot or something.  Can anyone offer advice on where I'm going wrong?</p>
  
  <p>Sorry.  The desired output should look like so:</p>
  
  <p><strong>Location</strong> | <strong>Apple</strong> | <strong>apple_comments</strong> | <strong>Orange</strong> | <strong>Orange_comments</strong> | <strong>Pear</strong> | <strong>Pear_comments</strong><br>
  Tennessee | 0 | NULL | 1 | very juicy | 1 | NULL<br>
  Florida | 1 | crisp | 1 | NULL | 0 | NULL<br>
  Virginia | 0 | NULL | 1 | NULL | 1 | tasty<br></p>
  
  <p>Essentially, merging the duplicates into one row.</p>
  
  <p>Thanks.</p>