SQL Pivot with multiple columns
21,134
Solution 1
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
Solution 2
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.
Author by
Admin
Updated on August 30, 2020Comments
-
Admin over 3 years
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.
-
Martin Smith almost 14 yearsLooks like a draw between the two approaches performance wise.
-
Cade Roux almost 14 years@Martin Smith - Mine has to UNPIVOT first. Because of that, I would have thought the traditional "pivot" would have been faster.
-
Mike M. almost 14 yearsCan't really tell based on this data set; would need to create some large test data. Even with this tiny data the cross tab is performing better (0ms vs 6ms). But check out the execution plans if you're interested in the differences.
-
Martin Smith almost 14 yearsI did already. Both showing 50% cost when run together. Both 1 table scan and remarkably similar execution plans. A few extra operators in the PIVOT/UNPIVOT but not contributing anything significant to the cost. Socks mildly ruffled perhaps but decidedly not blown off!
-
Mike M. almost 14 yearsI'm not able to access SQL right now but i'm fairly certain with a couple million rows we can see a good disparity. If I can't blow your socks off I'll eat 'em! :)
-
Martin Smith almost 14 years@Mike - Yep sorry. I just tried with half a million rows and you were absolutely correct a couple of those additional operators start becoming important and CPU time goes through the roof.
-
MikeTeeVee over 12 yearsAbsolutely Brilliant! I can now pivot and display as many columns as I want. Thank you! I would caveat this by saying that during the UnPivot process (before Pivoting at the end) all the columns are thrown into a single column. This is fine for this example, but if you're mixing numbers, dates, and strings, I would suggest casting everything as a VarChar to avoid errors (and then casting back to their original datatypes if needed).