What makes it unfriendly to work with is that the data is not normalized. I am trying to fix that by unpivoting in the following. Also, why did you choose to remove row 7 rather than 5 and 6? In any case, here is my attempt - not sure that will do it for you though. But the sample data I created should help someone else who may want to post a solution:CREATE TABLE #tmp(Room varchar(32), ID INT, starttime VARCHAR(4), endtime VARCHAR(4),Monday INT, Tuesday INT);INSERT INTO #tmp VALUES('GR4','1','0810','1700','9','9'),('GR4','2','0910','1300','4','4'),('GR4','3','0910','1700','0','0'),('GR4','4','1710','1800','0','0'),('GR4','5','1710','2000','0','3'),('GR4','6','1710','2000','3','0'),('GR4','7','1710','2000','3','3'),('GR4','8','1810','2100','0','0'),('LR11','1','0810','0900','0','1'),('LR11','2','0810','1000','0','0'),('LR11','3','0910','1000','0','1'),('LR11','4','0910','1100','2','0'),('LR11','5','1010','1100','0','0'),('LR11','6','1010','1200','0','0'),('LR11','7','1010','1200','0','2'),('LR11','8','1110','1200','0','0'),('LR11','9','1110','1200','1','0'),('LR11','10','1110','1300','0','0'),('LR11','11','1210','1300','0','0'),('LR11','12','1210','1300','0','1'),('LR11','13','1210','1400','2','0'),('LR11','14','1310','1400','0','0'),('LR11','15','1310','1500','0','0'),('LR11','16','1310','1600','0','3'),('LR11','17','1410','1600','0','0'),('LR11','18','1410','1600','2','0'),('LR11','19','1410','1700','0','0'),('LR11','20','1610','1800','0','2'),('LR11','21','1710','1800','0','0'),('LR11','22','1710','1900','2','0'),('LR11','23','1810','1900','0','0'),('LR11','24','1810','1900','0','1'),('LR11','25','1810','2000','0','0'),('LR11','26','1910','2000','0','0'),('LR11','27','1910','2000','0','1'),('LR11','28','2010','2100','1','0'),('LR11','29','2010','2300','0','3')SELECT Room, starttime,endtime, ISNULL(Monday,0) AS Monday, ISNULL(Tuesday,0) AS Tuesday FROM ( SELECT Room, starttime,endtime, hours, day FROM #tmp UNPIVOT (Hours FOR Day IN ([Monday],[Tuesday]))U WHERE Hours <> 0 GROUP BY Room, starttime,endtime, hours, DAY)s PIVOT (MAX(Hours) FOR DAY IN ([Monday],[Tuesday]))PDROP TABLE #tmp;