title: Merging two tables with some business logic for overfill
body: <p>I have a bit of a challenge ahead of me with a report I need to write.</p>
  
  <p>I have an ordered selected list of results, which has the following:</p>
  
  <pre><code>+---------+----------+----------+
  | Header  | estimate | TargetId |
  +---------+----------+----------+
  | Task 1  |       80 |        1 |
  | Task 2  |       30 |        1 |
  | Task 3  |       40 |        2 |
  | Task 4  |       10 |        2 |
  +---------+----------+----------+
  </code></pre>
  
  <p>I’d like to join this onto another set of data containing the Target information:</p>
  
  <pre><code>+--------+----------+
  | Target | Capacity |
  +--------+----------+
  |      1 |      100 |
  |      2 |       50 |
  |      3 |       50 |
  +--------+----------+
  </code></pre>
  
  <p>However I’d like to do some sort of pivot / cross join to fill each target to capacity and report this in a way to show a forecast of when each of the tasks for the target will be met.</p>
  
  <pre><code>+---------+----------+----------+----------+----------+---+---+
  | Header  | Overfill | Target 1 | Target 2 | Target 3 | … | … |
  +---------+----------+----------+----------+----------+---+---+
  | Task 1  | No       |       80 |        0 |        0 | 0 | 0 |
  | Task 2  | Yes      |       20 |       10 |        0 | 0 | 0 |
  | Task 3  | No       |        0 |       40 |        0 | 0 | 0 |
  | Task 4  | Yes      |        0 |        0 |       10 | 0 | 0 |
  +---------+----------+----------+----------+----------+---+---+
  </code></pre>
  
  <p>Alternatively displayed:</p>
  
  <pre><code>+---------+--------+-----------+
  | Header  | Target | Overfill% |
  +---------+--------+-----------+
  | Task 1  | 1      | 0         |
  | Task 2  | 1,2    | 33.33     |
  | Task 3  | 2      | 0         |
  | Task 4  | 3      | 100%      |
  +---------+--------+-----------+
  </code></pre>
  
  <p>The actual set of data will involve a few hundred tasks across 20 – 30 targets, unfortunately I don’t have any code to show as a demonstration, short of the few simple selects, as I’m not sure how to approach the overfill.</p>
  
  <p>I believe this could be achieved through C# easier however I was hoping this could be completed as a pure SP operation so I can return the data as I wish to display it.</p>
  
  <p>Any help or a nudge in the right direction to take would be greatly appreciated,
  Chris</p>