Deepak M

Deepak M

  • 913
  • 522
  • 32.7k

How to convert all columns data to two columns in SQL Server?

Dec 1 2022 11:23 AM

I have a table with the below structure and data.

sqlid logdate empcode timein1 timeout1 timein2 timeout2 timein3 timeout3 timein4 timeout4 timein5 timeout5
2 2022-08-24 5 10:15 14:03 14:18 19:41            
3 2022-08-24 6 11:02 13:57 14:41 15:48 15:55 16:19 16:33 19:45    
4 2022-08-24 7 09:35 11:13 11:31 16:47 17:13 18:08 18:55 19:23    
5 2022-08-24 9 10:03 12:45 12:56 14:05 14:41 18:11 18:22 20:04    
6 2022-08-24 10 09:51 10:31 11:48 13:52 14:28 16:41 17:21 18:25    
7 2022-08-24 12 09:58 13:52 14:28 16:24 16:40 17:20 18:13 18:36    
8 2022-08-24 16 09:24 13:17 13:56 19:01            
9 2022-08-24 18 09:41 10:57 12:42 13:53 13:59 17:13 17:40 17:58    
11 2022-08-24 29 09:19 17:37 22:53              
12 2022-08-24 31 09:57 13:17 14:02 14:18 15:55 16:10 16:31 18:41    

I want to convert the above data as below table structure.

empcode StartTime EndTime
5 10:15 14:03
5 14:18 19:41
6 11:02 13:57
6 14:41 15:48
6 15:55 16:19
6 16:33 19:45
7 09:35 11:13
7 11:31 16:47
7 17:13 18:08
7 18:55 19:23

Please help me to write a SQL Server query to get the output.


Answers (1)