SQL Pivot with multiple columns

Answer 1

I personally hate pivots- hard to read and unweidly.

CREATE TABLE #test
(
    WeekNo int,
    [DayOfWeek] int,
    FromTime time,
    ToTime time
    )

INSERT INTO #test
SELECT 1,2,'10:00','14:00'
UNION ALL
SELECT 1,3,'10:00','14:00'
UNION ALL
SELECT 2,3,'08:00','13:00'
UNION ALL
SELECT 2,4,'09:00','13:00'
UNION ALL
SELECT 2,5,'14:00','22:00'
UNION ALL
SELECT 3,1,'06:00','13:00'
UNION ALL
SELECT 3,4,'06:00','13:00'
UNION ALL
SELECT 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.

Answer 2

Here's the pivot version:

http://odata.stackexchange.com/stackoverflow/q/7305/so3241450

-- 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 (MIN(ColValue) FOR ColName IN ([Start1], [End1], [Start2], [End2], [Start3], [End3], [Start4], [End4], [Start5], [End5], [Start6], [End6], [Start7], [End7])) AS pvt​