Remove duplicate rows displayed in pivot

pivot, sql, t-sql

Solution

Remove the columns you don't need from the 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 query 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

Problem

Basically, I have created a pivot query wherein it will display the total_work_hours per each costcode for each employee. This is my desired output: ``` 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 inner 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 query: ``` 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 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. I need some guidance on how to solve this; can you help?

Original source