title: Remove duplicate rows displayed in pivot
body: <p>Basically, I have created a pivot query wherein it will display the total_work_hours per each costcode for each employee.</p>
  
  <p>This is my desired output:</p>
  
  <pre><code>employeeno  8322.170    10184.2648    8321.169    10184.2649 &lt;- costcodes
  --------------------------------------------------------------------------
  080418         10.00          1.50        NULL          NULL
  080441          6.50          NULL        1.00          3.00
  </code></pre>
  
  <p>but this is the result of my query:</p>
  
  <pre><code>employeeno  8322.170    10184.2648    8321.169    10184.2649 &lt;- costcodes
  --------------------------------------------------------------------------
  080418         10.00          NULL        NULL          NULL
  080418          NULL          1.50        NULL          NULL
  080441          NULL          NULL        1.00          NULL
  080441          6.50          NULL        NULL          NULL
  080441          NULL          NULL        NULL          3.00
  </code></pre>
  
  <p>This is the result of my inner query:</p>
  
  <pre><code>      employeeno    costcoding  hour_per_costcode        
        --------------------------------------------------
        PH080418   8322.170       10.00
        PH080418   10184.2648     1.50
        PH080441   8321.169       1.00
        PH080441   8322.170       6.50
        PH080441   10184.2649     3.00
  </code></pre>
  
  <p>This is my query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH PivotData AS  
      (SELECT wa.id,wa.sitecode, wa.companycode, wa.startdate, wa.enddate,  
              wa.description, wa.ratetypeid, wa_details.employeeno,  
              CAST(wa_details.costcode AS NVARCHAR(MAX)) + '.' +
              CAST(wa_details.subcostcode AS NVARCHAR(MAX)) costcoding ,
              wa_details.subcostcode, wa_details.hrswork [hour_per_costcode],  
              view_ttl_hours.ttl_work_hrs 
         FROM workallocation wa  
        INNER JOIN workallocation_details wa_details 
           ON wa.id = wa_details.workallocationid   
        INNER JOIN 
              (SELECT SUM(ttl_work_hrs) ttl_work_hrs, employee_id 
                 FROM vwu_SUM_TIMESHEET_DAILY  
                WHERE TKSDATE BETWEEN '02-09-2012' AND '02-09-2012' 
                GROUP BY employee_id
              ) view_ttl_hours 
           ON wa_details.employeeno=view_ttl_hours.employee_id  WHERE wa.id=99
      )
  SELECT employeeno, [8322.170], [10184.2648], [8321.169], [10184.2649]
    FROM PivotData   
   PIVOT (MAX([hour_per_costcode])          
          FOR costcoding         
           IN ([8322.170], [10184.2648], [8321.169], [10184.2649])
          ) AS PivotResult  
   ORDER BY employeeno;
  </code></pre>
  
  <p>My question is how will I be able to unite the value for my multiple rows so that my desire output will be achieve? I also try the cross tab query but the result is also the same.</p>
  
  <p>I need some guidance on how to solve this; can you help?</p>