SQL Pivot with multiple columns

2019-01-11 07:33发布

Need help with the pivot clause in sql server 2008. I have a table with this info:

Weekno    DayOfWeek     FromTime    ToTime
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

I want to convert this into a table that looks like this:

Week    Start1    End1    Start2    End2    Start3    End3    Start4    End4    Start5    End5    Start6    End6    Start7    End7
1                         10:00     14:00   10:00     14:00
2                                           08:00     13:00   09:00     13:00   14:00     22:00
3       06:00     13:00                                       06:00     13:00   14:00     22:00

Is there any way to do with a pivot query? Please write respond with an example on how to do it.

I appreciate any kind of help on this. Thanks in advance.

3条回答
祖国的老花朵
2楼-- · 2019-01-11 08:16

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    ToTime
1         2             10:00       14:00
1         2             07:00       09:00
2         3             08:00       13:00
2         4             09:00       13:00

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

查看更多
我只想做你的唯一
3楼-- · 2019-01-11 08:31

Here's the pivot version:

https://data.stackexchange.com/stackoverflow/query/7295/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​
查看更多
Rolldiameter
4楼-- · 2019-01-11 08:31

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.

查看更多
登录 后发表回答