SQL Pivot with multiple columns

Here's the pivot version:

https://data.stackexchange.com/stackoverflow/query/7295/so3241450

-- SO3241450CREATE 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, ColValueFROM BaseUNPIVOT (ColValue FOR ColName IN ([Start], [End])) AS pvt)SELECT *FROM normPIVOT (MIN(ColValue) FOR ColName IN ([Start1], [End1], [Start2], [End2], [Start3], [End3], [Start4], [End4], [Start5], [End5], [Start6], [End6], [Start7], [End7])) AS pvt​

I personally hate pivots- hard to read and unweidly.

CREATE TABLE #test(    WeekNo int,    [DayOfWeek] int,    FromTime time,    ToTime time    )INSERT INTO #testSELECT 1,2,'10:00','14:00'UNION ALLSELECT 1,3,'10:00','14:00'UNION ALLSELECT 2,3,'08:00','13:00'UNION ALLSELECT 2,4,'09:00','13:00'UNION ALLSELECT 2,5,'14:00','22:00'UNION ALLSELECT 3,1,'06:00','13:00'UNION ALLSELECT 3,4,'06:00','13:00'UNION ALLSELECT 3,5,'14:00','22:00'SELECT WeekNo,     MAX(CASE WHEN DayOfWeek = 1 THEN FromTime ELSE NULL END)  AS Start1,    MAX(CASE WHEN DayOfWeek = 1 THEN ToTime ELSE NULL END)  AS End1,    MAX(CASE WHEN DayOfWeek = 2 THEN FromTime ELSE NULL END)  AS Start2,    MAX(CASE WHEN DayOfWeek = 2 THEN ToTime ELSE NULL END)  AS End2,    MAX(CASE WHEN DayOfWeek = 3 THEN FromTime ELSE NULL END)  AS Start3,    MAX(CASE WHEN DayOfWeek = 3 THEN ToTime ELSE NULL END)  AS End3,    MAX(CASE WHEN DayOfWeek = 4 THEN FromTime ELSE NULL END)  AS Start4,    MAX(CASE WHEN DayOfWeek = 4 THEN ToTime ELSE NULL END)  AS End4,    MAX(CASE WHEN DayOfWeek = 5 THEN FromTime ELSE NULL END)  AS Start5,    MAX(CASE WHEN DayOfWeek = 5 THEN ToTime ELSE NULL END)  AS End5,    MAX(CASE WHEN DayOfWeek = 6 THEN FromTime ELSE NULL END)  AS Start6,    MAX(CASE WHEN DayOfWeek = 6 THEN ToTime ELSE NULL END)  AS End6,    MAX(CASE WHEN DayOfWeek = 7 THEN FromTime ELSE NULL END)  AS Start7,    MAX(CASE WHEN DayOfWeek = 7 THEN ToTime ELSE NULL END)  AS End7    FROM #test    GROUP BY WeekNo

And it'll blow the socks off of a pivot; performance wise.

I think the CASE WHEN will only work if there are only unique Weekno and DayofWeek as it will only return records of latest start and end time and filter out the rest.Example

Weekno    DayOfWeek     FromTime    ToTime1         2             10:00       14:001         2             07:00       09:002         3             08:00       13:002         4             09:00       13:00

It will only return the first row of weekno 1 of DayofWeek 2 and skip the second row.