Sql Server pivot table not grouping a result set

Question

I have a 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 wrote this query to summarize all vacation types as columns for each employee. Each employee should have only one line.

 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 does not give me the expected result. For each employee, there are several lines that are not what I want.

The following table shows the expected result -

 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 a group in the request. Because what a bar does is that it is "grouped" into other columns. The key to this decision is internal choice. I think that it is not idé god to first make an amount with a group and then apply the amount and group 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 give you the same result.

Answer 2

you can try the following. You can cancel your table since I checked the temporary table in the same way as your table.

 create table #tmp_emp (EMP_CODE varchar(10),LEAVENAME char(2), APP_TYPE char(1),LEAVE_DATE datetime,ACT_DAYS decimal(2,1)) insert into #tmp_emp values ('ST006','CL','P ','2012-01-03','1.0'); insert into #tmp_emp values ('ST006','CL','P ','2012-01-18','1.0'); insert into #tmp_emp values ('ST006','SL','P ','2012-01-27','1.0'); insert into #tmp_emp values ('ST002','CL','P ','2012-01-04','1.0'); insert into #tmp_emp values ('ST002','CL','P ','2012-01-12','1.0'); insert into #tmp_emp values ('ST002','SL','P ','2012-01-27','1.0'); insert into #tmp_emp values ('OCO038','CL','P ','2012-01-27','1.0'); insert into #tmp_emp values ('HO188','CL','P ','2012-01-09','1.0'); insert into #tmp_emp values ('HO188','CL','P ','2012-01-30','1.0'); insert into #tmp_emp values ('HO085','CL','P ','2012-01-19','1.0'); insert into #tmp_emp values ('HO085','SL','P ','2012-01-23','1.0'); SELECT EMP_CODE,[CL],[LWP],[PL],[SL] FROM ( select EMP_CODE, LEAVENAME, sum(ACT_DAYS) ACT_DAYS from #tmp_emp group by EMP_CODE, LEAVENAME ) L PIVOT (SUM(ACT_DAYS) FOR LEAVENAME IN ([CL],[LWP],[PL],[SL])) AS PVT ORDER BY EMP_CODE;