StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORemove duplicate rows displayed in pivot
text
Body
copied!<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 <- 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 <- 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload