CODEWITHSUNDEEP
sqlsql-server
Prabhakaran
Prabhakaran

Reputation: 1337

How to convert two rows as single row result in SQL Server?

I have a table as like below:

EmployeeCode  DateStart CompanyCode DepartmentCode
-----------------------------------------------------
EMP1         2018-01-01   COM1          DEP1
EMP1         2018-02-01   COM1          DEP2
EMP1         2019-03-01   COM1          DEP1
EMP2         2000-01-01   COM1          DEP1
EMP2         2000-01-01   COM1          DEP2
EMP2         2010-03-01   COM1          DEP1
EMP3         2000-01-01   COM3          DEP3
EMP3         2000-02-01   COM3          DEP3
EMP3         2012-03-01   COM1          DEP2

and I am expecting a output like below

How to know there is different DateStart with same CompanyCode and DepartmentCode in subsequent per EmployeeCode?

 EmployeeCode DateStart CompanyCode DepartmentCode EmployeeCode DateStart CompanyCode DepartmentCode
--------------------------------------------------------------------------------------------------------
    EMP3         2000-01-01 COM3        DEP3          EMP3         2000-02-01 COM3         DEP3

I tried this query:

select 
    Emp1.EmployeeCode, Emp2.DateStart, Emp1.CompanyCode, Emp1.DepartmentCode 
from
    (select 
         EmployeeCode, CompanyCode, DepartmentCode,
         datepart(year, DateStart) as StartDate 
     from
         TblEmployeeDetail 
     group by 
         EmployeeCode, CompanyCode, DepartmentCode, datepart(year,DateStart) 
     having 
         count(*) > 1) as Emp1
join 
    TblEmployeeDetail Emp2 on Emp1.CompanyCode = Emp2.CompanyCode 
                           and Emp1.DepartmentCode = Emp2.DepartmentCode 
                           and Emp1.EmployeeCode = Emp2.EmployeeCode
                           and Emp1.StartDate = datepart(year, Emp2.DateStart)

Upvotes: 0

Views: 55

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below way - use min() and max() to get two separate date

select EmployeeCode,CompanyCode,DepartmentCode,min(datestart) as datestart1,max(datestart) as datestart2, 
from TblEmployeeDetail 
group by EmployeeCode,CompanyCode,DepartmentCode,DATEPART(year,DateStart) 
having count(*) > 1

Upvotes: 1

Related Questions