Convert Rows into Columns in SQL Server

Last Reply one month ago By akhter

Posted one month ago

I have data in Rows like

DateTime                  Status        EmpId

2018-05-28 8:00        01            2000347  

2018-05-28 20:18       02           2000347  

2018-05-28 8:00        01            2000348  

2018-05-28 17:18       02           2000348

 

Now i want above data convert into columns like this

EmpID               Status (1)INTime         Status(2)OutTime    

2000347            2018-05-28 8:00        2018-05-28 20:18

2000348            2018-05-28 8:00        2018-05-28 17:18

 

Kindly help ..

Posted one month ago

Find this solution 

 

create table #temp (dDateTime Datetime, sStatus varchar(20), sEmpId varchar(20)) 
insert into #temp values ('2018-05-28 8:00', 's1', '2000347')
insert into #temp values ('2018-05-28 20:18', 's2', '2000347')
insert into #temp values ('2018-05-28 8:00', 's1', '2000348') 
insert into #temp values ('2018-05-28 17:18', 's2', '2000348') 
--select * from #temp  
   
select sEmpId, s1 as 'INOUT (1)INTime', s2 as 'INOUT(2)OutTime' 
from 
( 
  select *
  from #temp 
) d 
pivot 
( 
  max(dDateTime) 
  for sStatus in (s1,s2) 
) piv; 
   
drop table #temp