Q&A for pro webmasters
-- SO3241450 CREATE TABLE #SO3241450 ( Weekno int NOT NULL ,DayOfWeek int NOT NULL ,FromTime time NOT NULL ,ToTime time NOT NULL ) INSERT INTO #SO3241450 VALUES (1, 2, '10:00', '14:00') ,(1, 3, '10:00', '14:00') ,(2, 3, '08:00', '13:00') ,(2, 4, '09:00', '13:00') ,(2, 5, '14:00', '22:00') ,(3, 1, '06:00', '13:00') ,(3, 4, '06:00', '13:00') ,(3, 5, '14:00', '22:00') ;WITH Base AS ( SELECT Weekno, DayOfWeek, FromTime AS [Start], ToTime AS [End] FROM #SO3241450 ) ,norm AS ( SELECT Weekno, ColName + CONVERT(varchar, DayOfWeek) AS ColName, ColValue FROM Base UNPIVOT (ColValue FOR ColName IN ([Start], [End])) AS pvt ) SELECT * FROM norm --PIVOT (ColValue FOR ColName IN ([Start1], [End1], [Start2], [End2], [Start3], [End3], [Start4], [End4], [Start5], [End5], [Start6], [End6], [Start7], [End7])) AS pvt