title: Join table on itself for unique row combinations for calculations
body: <p>I have a table that I need to use to build a result set from where certain rows from the table are columns in the result set. I started to chain <code>LEFT JOIN</code>s together on the table multiple times but I need to eliminate results that are a different combination of another result already in the set:</p>
  
  <p>For example, if I get 1, 21, 25 as result columns, I can't have ANY other combination of those numbers in the results.</p>
  
  <p>My table definition is:</p>
  
  <pre><code>Table tblKPIDetails
  Column Month int
  Column Year int
  Column Division varchar(3)
  Column KPI int
  Column Value decimal(18,4)
  </code></pre>
  
  <p>My current query is:</p>
  
  <pre><code>SELECT *
  FROM tblKPIDetails J1
      LEFT JOIN tblKPIDetails J2 ON J2.Month = J1.Month AND J2.Year = J1.Year  AND J2.Division = J1.Division AND NOT(J2.KPI = J1.KPI ) AND (J2.KPI = 1 OR J2.KPI = 21 OR J2.KPI = 25)
      LEFT JOIN tblKPIDetails J3 ON J3.Month = J1.Month AND J3.Year = J1.Year  AND J3.Division = J1.Division AND NOT(J3.KPI = J1.KPI ) AND (J3.KPI = 1 OR J3.KPI = 21 OR J3.KPI = 25)
  WHERE J1.KPI = 1 OR J1.KPI = 21 OR J1.KPI = 25
  </code></pre>
  
  <p>I know this is wrong, but it's a super-set of what I need. In the results from the query above, I can get J1.KPI, J2.KPI, J3.KPI or J1.KPI, J3.KPI, J2.KPI, or any other combination.</p>
  
  <p>My expected result would be:</p>
  
  <pre><code>Division | Month | Year | KPIA | KPIAValue | KPIB | KPIBValue | KPIC | KPICValue
  </code></pre>
  
  <p>for each division, month, and year</p>
  
  <p>where KPIA, KPIB, or KPIC = 1, 21, or 25 but only 1 combination of 1,21,25 exists per division|month|year</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>To clarify the expected results a little more, using the above query, I'm getting the following results:</p>
  
  <pre><code>Division | Month | Year | KPIA | KPIAValue | KPIB | KPIBValue | KPIC | KPICValue
  --------------------------------------------------------------------------------
  000          1     2012     1      1000       21      2000       25     3000
  000          1     2012    21      2000        1      1000       25     3000
  000          1     2012    25      3000       21      2000        1     1000
  111          1     2012     1      555        21      10000      25     5000
  </code></pre>
  
  <p>I need to make it so my results would only be ANY 1 of the first 3 results and then the last one...for example:</p>
  
  <pre><code>Division | Month | Year | KPIA | KPIAValue | KPIB | KPIBValue | KPIC | KPICValue
  --------------------------------------------------------------------------------
  000          1     2012    25      3000       21      2000        1     1000
  111          1     2012     1      555        21      10000      25     5000
  </code></pre>