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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With