Sql Server pivot table not grouping result set

Question

I have table with values as follows -

EMP_CODE | LEAVENAME | APP_TYPE |  LEAVE_DATE | ACT_DAYS
--------------------------------------------------------
ST006    |  CL       | P        | 2012-01-03  | 1.0
ST006    |  CL       | P        | 2012-01-18  | 1.0
ST006    |  SL       | P        | 2012-01-27  | 1.0
ST002    |  CL       | P        | 2012-01-04  | 1.0
ST002    |  CL       | P        | 2012-01-12  | 1.0
ST002    |  SL       | P        | 2012-01-27  | 1.0
OCO038   |  CL       | P        | 2012-01-27  | 1.0
HO188    |  CL       | P        | 2012-01-09  | 1.0
HO188    |  CL       | P        | 2012-01-30  | 1.0
HO085    |  CL       | P        | 2012-01-19  | 1.0
HO085    |  SL       | P        | 2012-01-23  | 1.0

I have written this query to sum all leave types as columns for each employee. Each employee must have only one row.

SELECT EMP_CODE,[CL],[LWP],[PL],[SL] FROM LEAVE_DETAIL L 
PIVOT (SUM(ACT_DAYS) FOR LEAVENAME IN ([CL],[LWP],[PL],[SL])) 
AS PVT ORDER BY EMP_CODE;

But this query is not giving me the expected output. There are more than one row for each employee which is not what I want.

The following table show the expected output -

EMP_CODE |  CL  | SL  |
---------|------|-----|
ST006    | 2.0  | 1.0 |
ST002    | 2.0  | 1.0 |
OCO038   | 1.0  | 0.0 |
HO188    | 2.0  | 0.0 |
HO085    | 1.0  | 1.0 |

Please help.

Answer 1

You don't even need the group by in the query. Because what a pivot does is that it "group by" on the other columns. The key to this solution is the inner select. I think it is not a god idé to first do a sum with group by and then apply a sum and a group by again.

SELECT 
    EMP_CODE,
    [CL],
    [LWP],
    [PL],
    [SL] 
FROM 
(
    SELECT 
        EMP_CODE, 
        LEAVENAME, 
        ACT_DAYS
    FROM 
        @tmp_emp
) L 
PIVOT
(
    SUM(ACT_DAYS) 
    FOR LEAVENAME IN ([CL],[LWP],[PL],[SL])
) 
AS PVT 
ORDER BY EMP_CODE

This will get you the same result.