sql - 删除数据透视表中显示的重复行

Question

基本上，我创建了一个数据透视查询，其中它将显示每个员工每个成本代码的 total_work_hours。

这是我想要的输出:

employeeno  8322.170    10184.2648    8321.169    10184.2649 <- costcodes
--------------------------------------------------------------------------
080418         10.00          1.50        NULL          NULL
080441          6.50          NULL        1.00          3.00

但这是我查询的结果:

employeeno  8322.170    10184.2648    8321.169    10184.2649 <- 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

这是我内部查询的结果:

      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

这是我的查询:

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;

我的问题是如何将多行的值统一起来以实现我想要的输出？我也尝试了交叉表查询，但结果也是一样的。

我需要一些关于如何解决这个问题的指导；你能帮忙吗？

Answer 1

从 CTE 中删除不需要的列。像这样的东西应该适合你。

WITH PivotData AS  
    (SELECT 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]
       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;

上面查询你的数据:

WITH PivotData AS  
    (select 'PH080418' as employeeno,   8322.170 as costcoding, 10.00 as hour_per_costcode union all
     select 'PH080418', 10184.2648, 1.50 union all
     select 'PH080441', 8321.169,   1.00 union all
     select 'PH080441', 8322.170,   6.50 union all
     select 'PH080441', 10184.2649, 3.00
    )
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;

结果:

employeeno 8322.170   10184.2648   8321.169   10184.2649
---------- ---------- ------------ ---------- -----------
PH080418   10.00      1.50         NULL       NULL
PH080441   6.50       NULL         1.00       3.00