Нуждаюсь в помощи с пунктом центра в SQL-сервере 2008. У меня есть таблица с этой информацией:
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
Я хочу преобразовать это в таблицу, которая похожа на это:
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
Там какой-либо путь состоит в том, чтобы сделать с запросом центра? Запишите, отвечают примером о том, как сделать это.
Я ценю любой вид справки на этом.Заранее спасибо.
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
Лично я ненавижу повороты - трудно читать и невзрачно.
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
И он сорвет носки с оси; производительность мудрая.