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.
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
It will only return the first row of weekno 1 of DayofWeek 2 and skip the second row.
Here's the pivot version:
https://data.stackexchange.com/stackoverflow/query/7295/so3241450
I personally hate pivots- hard to read and unweidly.
And it'll blow the socks off of a pivot; performance wise.