Geek asks and answers

Delete duplicate rows displayed in a vault

Basically, I created a consolidated query that displays total_work_hours for each cost calculator for each employee.

This is my desired result:

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

but this is the result of my query:

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

This is the result of my internal query:

      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

This is my request:

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;

My question is, how can I combine the value for multiple rows to achieve my result? I also try cross tabulation request, but the result is the same too.

I need some guidance on how to solve this; you can help?

+3
source share
1 answer

Remove columns that you do not need from CTE. Something like this should work for you.

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;

Above the request with your data:

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;

Result:

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

More articles:


All Articles