March 2, 2018 at 12:29 pm
Hi guys.I need help with query results output.
I have a driver report results which looks looks this
Drv DOW Shift
1 null null
2 Sunday 06:00-12:00
2 Monday 06:00 -11:00
2 Tuesday 06:00-12:00
2 Wednesday 06:00 -11:00
2 Thursday 06:00-12:00
2 Friday 04:00 -11:00
2 Saturday 04:00 -10:00
.
.
.
and i want to rotate the DOW column so it will look like this
Drv Sunday Monday Tuesday Wednesday Thursday Friday Saturday
1 null null null null null null null
2 06:-12:00 06:00-11:00 06:00-12:00 06:00-11:00 06:00-12:00 04:00-11:00 04:00-10:00
.
.
i tried to use PIVOT :
select drv,'Sunday', 'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'
from
(select dow,shift
from mySource
pivot
(shift for dow in ('Sunday', 'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday' )
)as pvt
And it did not work.Any suggestions?
Thank you
March 2, 2018 at 12:50 pm
Try this:DECLARE @DriverData AS TABLE (
Drv int,
DOW varchar(9),
[Shift] varchar(13)
);
INSERT INTO @DriverData (Drv, DOW, [Shift])
VALUES (1, null, null),
(2, 'Sunday', '06:00-12:00'),
(2, 'Monday', '06:00 -11:00'),
(2, 'Tuesday', '06:00-12:00'),
(2, 'Wednesday', '06:00 -11:00'),
(2, 'Thursday', '06:00-12:00'),
(2, 'Friday', '04:00 -11:00'),
(2, 'Saturday', '04:00 -10:00');
SELECT Drv, [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday]
FROM @DriverData
PIVOT (MIN([Shift]) FOR DOW IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])) AS UPVT
ORDER BY Drv;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 2, 2018 at 1:05 pm
Barcelona10 - Friday, March 2, 2018 12:57 PMWhoo hooo it worked.Thank you very much sgmunson
Glad I could help. All you had missed was to remember that PIVOT requires an AGGREGATE function, like COUNT, SUM, MIN, MAX, AVG. It's UNPIVOT that doesn't use aggregates. When you usually code CROSS TAB queries, you tend to forget which is which, as I had, until I just tried your query and then just tried using the MAX aggregate. In your case, either MIN or MAX would work with the data provided. There are data scenarios that could cause you trouble.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply