Help in SQL Server PIVOT

Question

Hi
I have a table with columns as below.

EmpName EmpTraining AssignmentDate CompletedDate
Emp1 Training1 2020-10-01 2020-10-21
Emp1 Training1 2019-10-01 2019-10-15

I need o/p as below

EmpName EmpTraining CYAssignDate CYCompDate LYAssigndate LYCompDate
Emp1 Training1 2020-10-01 2020-10-21 2019-10-01 2019-10-15

Please help in achieving the same.
Thanks

Answer 1

It can also be done without using PIVOT

Create Table #Sample(EmpName varchar(20), EmpTraining varchar(20), AssignmentDate Date, CompletedDate Date);
Insert #Sample(EmpName, EmpTraining, AssignmentDate, CompletedDate) Values
('Emp1', 'Training1', '2020-10-01', '2020-10-21'),
('Emp1', 'Training1', '2019-10-01', '2019-10-15');

Select EmpName, EmpTraining, 
  Min(Case When DateDiff(year, AssignmentDate, GetDate()) = 0 Then AssignmentDate End) As CYAssgnDate,
  Min(Case When DateDiff(year, CompletedDate, GetDate()) = 0 Then CompletedDate End) As CYCompDate,
  Min(Case When DateDiff(year, AssignmentDate, GetDate()) = 1 Then AssignmentDate End) As LYAssgnDate,
  Min(Case When DateDiff(year, CompletedDate, GetDate()) = 1 Then CompletedDate End) As LYCompDate
From #Sample
Group By EmpName, EmpTraining;

Tom

Answer 2

Hi @Ganesh Sundaresan ,

Please refer below:

declare @table table  
(EmpName varchar(10),  
EmpTraining varchar(20),  
AssignmentDate date,  
CompletedDate date)  
  
insert into @table values  
('Emp1','Training1','2020-10-01','2020-10-21'),  
('Emp1','Training1','2019-10-01','2019-10-15')  
  
;with cte as (select *,  DATEPART(YEAR,AssignmentDate) [year] from @table)  
  
select EmpName,EmpTraining,  
CYAssignmentDate as CYAssignDate,  
CYCompletedDate as CYCompDate,   
LYAssignmentDate as LYAssigndate,   
LYCompletedDate LYCompDate  
from  
(  
  select EmpName,EmpTraining,  
    case when [year]=2020    
      then 'CY'+col  
      else 'LY'+col end   
   col,   
    value  
  from cte  
  unpivot  
  (  
    value  
    for col in (AssignmentDate, CompletedDate)  
  ) unpiv  
) src  
pivot  
(  
  max(value)  
  for col in (CYAssignmentDate,CYCompletedDate,LYAssignmentDate,LYCompletedDate)  
) piv  

Output:

EmpName EmpTraining CYAssignDate CYCompDate LYAssigndate LYCompDate  
Emp1 Training1 2020-10-01 2020-10-21 2019-10-01 2019-10-15  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If you need a pivot-based solution, but also are interested in alternatives, then check this interpretation too:

declare @table as table ( EmpName varchar(max), EmpTraining varchar(max), AssignmentDate date, CompletedDate date )
set dateformat ymd
insert @table values
( 'Emp1', 'Training1', '2020-10-01', '2020-10-21' ),
( 'Emp1', 'Training1', '2019-10-01', '2019-10-15' )

select * from @table

---

select 
    t1.EmpName, 
    t1.EmpTraining, 
    t1.AssignmentDate as CYAssignDate,
    t1.CompletedDate as CYCompDate,
    t2.AssignmentDate as LYAssignDate,
    t2.CompletedDate as LYCompDate
from @table as t1
left join @table as t2 on
    t2.EmpName = t1.EmpName and t2.EmpTraining = t1.EmpTraining and
    DATEPART(yyyy, t2.AssignmentDate) = DATEPART(yyyy, t1.AssignmentDate) - 1 and 
    DATEPART(m, t2.AssignmentDate) = DATEPART(m, t1.AssignmentDate)
where DATEPART(yyyy, t1.AssignmentDate) = DATEPART(yyyy, GETDATE())

Answer 4

Hi @Ganesh Sundaresan ,

You could also refer below query and check if you would like not to use complex pivot and unpivot functions.

 declare @table table  
 (EmpName varchar(10),  
 EmpTraining varchar(20),  
 AssignmentDate date,  
 CompletedDate date)  
      
 insert into @table values  
 ('Emp1','Training1','2020-10-01','2020-10-21'),  
 ('Emp1','Training1','2019-10-01','2019-10-15')  
   
select EmpName,EmpTraining,  
       max(case when seqnum = 1 then AssignmentDate end) as CYAssignDate,  
       max(case when seqnum = 1 then CompletedDate end) as CYCompDate,  
       max(case when seqnum = 2 then AssignmentDate end) as LYAssigndate,  
       max(case when seqnum = 2 then CompletedDate end) as LYCompDate  
from (select a.*,  
             row_number() over (partition by EmpName, EmpTraining order by EmpName, EmpTraining) as seqnum  
      from @table a  
     ) t  
group by EmpName,EmpTraining  

Output:

 EmpName EmpTraining CYAssignDate CYCompDate LYAssigndate LYCompDate  
 Emp1 Training1 2020-10-01 2020-10-21 2019-10-01 2019-10-15  

Note: You have to specify the number of columns in the output. If you don't know how many there are, you can either:

1. Generate a dynamic SQL statement to do the count in advance.
2. Manually count yourself and add the appropriate columns.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.