Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Remove the columns you don't need from the CTE. Something like this should work for you.</p> <pre><code>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; </code></pre> <p>Above query with your data:</p> <pre><code>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; </code></pre> <p>Result:</p> <pre><code>employeeno 8322.170 10184.2648 8321.169 10184.2649 ---------- ---------- ------------ ---------- ----------- PH080418 10.00 1.50 NULL NULL PH080441 6.50 NULL 1.00 3.00 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PORemove duplicate rows displayed in pivot
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMikael Eriksson
    1. USMikael Eriksson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PORemove duplicate rows displayed in pivot
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COsorry for the late reply. I think no one understand my problem. I want my rows to be unique. Please read my description. Thanks!
      singulars
      1. PO
      1. USIce_Drop
    2. CO@user1378857 Did you try the query I provided to you? What was the output?
      singulars
      1. PO
      1. USMikael Eriksson
    3. COyes i tried the query you gave to me. The result is the same with my query. The rows are not unique.
      singulars
      1. PO
      1. USIce_Drop
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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