i****a
发帖数: 36252 | 1 Day Open Close
1 08:00 06:00
2 08:00 06:00
3 08:00 06:00
4 08:00 06:00
5 08:00 06:00
6 09:00 05:00
7
so this is the hours of a shop
The goal is to get this into the following format:
Mon - Fri 08:00-06:00, Sat 09:00-05:00
How do I do this?? |
c**t
发帖数: 2744 | 2 google pivot query
【在 i****a 的大作中提到】
: Day Open Close
: 1 08:00 06:00
: 2 08:00 06:00
: 3 08:00 06:00
: 4 08:00 06:00
: 5 08:00 06:00
: 6 09:00 05:00
: 7
: so this is the hours of a shop
: The goal is to get this into the following format:
|
i****a
发帖数: 36252 | 3 it's not just a pivot
need to combine the dates. if M and T are the same, T-F same, S and S
same, for example, then show:
Mon - Tue 07:00-06:00, Tue - Fri 08:00-06:00, Sat - Sun 10:00-02:00
I couple pivot it into individual columns for MonOpen, MonClose etc and
use a whole bunch of case statements but that's ugly...
【在 c**t 的大作中提到】
: google pivot query
|
a9
发帖数: 21638 | 4 用cursor应该比较方便了吧？
【在 i****a 的大作中提到】
: Day Open Close
: 1 08:00 06:00
: 2 08:00 06:00
: 3 08:00 06:00
: 4 08:00 06:00
: 5 08:00 06:00
: 6 09:00 05:00
: 7
: so this is the hours of a shop
: The goal is to get this into the following format:
|
i****a
发帖数: 36252 | 5 trying to avoid cursor and loops...
【在 a9 的大作中提到】
: 用cursor应该比较方便了吧？
|
j*****n
发帖数: 1781 | 6 check "GROUPING function" in BOL see if it can work in your case.
sorry, no time to think it in deep yet... |
b******g
发帖数: 81 | 7 I don't think SQL is the best option for this functionality.
Are you sure SQL is the only way/tool provided? |
a9
发帖数: 21638 | 8 cursor and loops exists for a reason
【在 i****a 的大作中提到】
: trying to avoid cursor and loops...
|
f*******h
发帖数: 53 | 9 Try this one, in sqlserver 2008:
;with cte as (
select 1 as GroupID,DAY,opentime,closetime
from OfficeHours
where DAY=1
union all
select cte.GroupID,o.Day,o.OpenTime,o.CloseTime
from cte,
OfficeHours as o
where o.Day=cte.Day+1
and cte.CloseTime=o.CloseTime
and cte.OpenTime=o.OpenTime
union all
select cte.GroupID+1,o.Day,o.OpenTime,o.CloseTime
from cte,
OfficeHours as o
where o.Day=cte.Day+1
|
f*******h
发帖数: 53 | 10 If you are using Oracle 11g, just use "within group" method |
i****a
发帖数: 36252 | 11 oh, did I not list the system I am using? It's SQL 2005. Thanks freshfish. I
'll look at the code you provided. |
i****a
发帖数: 36252 | 12 Excellent, freshfish's code works! Thanks a bunch
I just need to do a row concatenation to get it into 1 line. |
f*******h
发帖数: 53 | 13 Thanks, you just made me rich:) |
