具有多列的 SQL Pivot
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3241450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Pivot with multiple columns
提问by Svein Thomas
Need help with the pivot clause in sql server 2008. I have a table with this info:
需要有关 sql server 2008 中的 pivot 子句的帮助。我有一个包含以下信息的表：
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.
我很感激这方面的任何帮助。提前致谢。
回答by Cade Roux
Here's the pivot version:
这是枢轴版本：
https://data.stackexchange.com/stackoverflow/query/7295/so3241450
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?
回答by Mike M.
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.
它会把袜子从枢轴上吹下来；性能明智。
回答by tom cat
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
我认为 CASE WHEN 只有在只有唯一的 Weekno 和 DayofWeek 时才有效，因为它只会返回最新开始和结束时间的记录并过滤掉其余时间。例子
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.
它只会返回 DayofWeek 2 的 weekno 1 的第一行并跳过第二行。
