Looks like there have been some new posts since I started in on my version of the solution... I haven't had a chance to test any of them against what I came up with.
For better or worse, I wrote it... It seems to meet the requirements... So I'm posting it. 😀
-- Create test data --
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
StoreID INT NOT NULL PRIMARY KEY,
MonOpen VARCHAR(10),
MonClosed VARCHAR(10),
TueOpen VARCHAR(10),
TueClosed VARCHAR(10),
WedOpen VARCHAR(10),
WedClosed VARCHAR(10),
ThuOpen VARCHAR(10),
ThuClosed VARCHAR(10),
FriOpen VARCHAR(10),
FriClosed VARCHAR(10),
SatOpen VARCHAR(10),
SatClosed VARCHAR(10),
SunOpen VARCHAR(10),
SunClosed VARCHAR(10)
);
INSERT #temp (StoreID,MonOpen,MonClosed,TueOpen,TueClosed,WedOpen,WedClosed,ThuOpen,ThuClosed,FriOpen,FriClosed,SatOpen,SatClosed,SunOpen,SunClosed) VALUES
(1,'7:30 AM','5:30 PM','7:30 AM','5:30 PM','7:30 AM','5:30 PM','7:30 AM','5:30 PM','7:30 AM','5:30 PM','Closed',NULL,'Closed',NULL),
(2,'7:00 AM','6:00 PM','7:00 AM','6:00 PM','7:00 AM','7:00 PM','7:00 AM','6:00 PM','7:00 AM','6:00 PM','8:00 AM','5:00 PM','Closed', NULL);
-- The solution --
WITH UnPiv AS (-- Start by unpivoting the data
SELECT
x.StoreID, x.DayOrder, x.DayAbbr, x.OpenTime, x.ClosedTime
FROM
#temp t
CROSS APPLY (VALUES
(t.StoreID, 1, 'Mon', t.MonOpen, t.MonClosed),
(t.StoreID, 2, 'Tue', t.TueOpen, t.TueClosed),
(t.StoreID, 3, 'Wed', t.WedOpen, t.WedClosed),
(t.StoreID, 4, 'Thu', t.ThuOpen, t.ThuClosed),
(t.StoreID, 5, 'Fri', t.FriOpen, t.FriClosed),
(t.StoreID, 6, 'Sat', t.SatOpen, t.SatClosed),
(t.StoreID, 7, 'Sun', t.SunOpen, t.SunClosed)
) x (StoreID, DayOrder, DayAbbr, OpenTime, ClosedTime)
), GetRanges AS (-- Then identify the changes in store hours between days. If a day is the same as the previous day, leave it null.
SELECT
up.StoreID,
up.DayOrder,
up.DayAbbr,
up.OpenTime,
up.ClosedTime,
CASE
WHEN CONCAT(up.OpenTime, up.ClosedTime) <> COALESCE(LAG(CONCAT(up.OpenTime, up.ClosedTime), 1) OVER (PARTITION BY up.StoreID ORDER BY up.DayOrder), '')
THEN ROW_NUMBER() OVER (PARTITION BY up.StoreID ORDER BY up.DayOrder)
END AS NewRangeID
FROM
UnPiv up
), RangeSmear AS (-- Smear the non-null values into the null values to create solid range blocks
SELECT
gr.StoreID,
gr.DayOrder,
gr.DayAbbr,
gr.OpenTime,
gr.ClosedTime,
MAX( gr.NewRangeID) OVER (PARTITION BY gr.StoreID ORDER BY gr.DayOrder ROWS UNBOUNDED PRECEDING) AS RangeSmear
FROM
GetRanges gr
)
-- The final select...
SELECT
rs.StoreID,
CASE
WHEN MIN(rs.DayOrder) = MAX(rs.DayOrder)
THEN CAST(SUBSTRING(MIN(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3))
ELSE CAST(SUBSTRING(MIN(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3)) + ' - ' +
CAST(SUBSTRING(MAX(CAST(rs.DayOrder AS BINARY(4)) + CAST(rs.DayAbbr AS BINARY(4))), 5, 4) AS CHAR(3))
END AS StoreDays,
CASE
WHEN rs.OpenTime IS NULL THEN rs.ClosedTime
WHEN rs.ClosedTime IS NULL THEN OpenTime
ELSE CONCAT(rs.OpenTime, ' - ', rs.ClosedTime)
END AS HoursOfOperation
FROM
RangeSmear rs
GROUP BY
rs.StoreID,
rs.OpenTime,
rs.ClosedTime,
rs.RangeSmear
ORDER BY
rs.StoreID,
MIN(rs.DayOrder)
Here are the results...
StoreIDStoreDaysHoursOfOperation
1Mon - Fri7:30 AM - 5:30 PM
1Sat - SunClosed
2Mon - Tue7:00 AM - 6:00 PM
2Wed7:00 AM - 7:00 PM
2Thu - Fri7:00 AM - 6:00 PM
2Sat8:00 AM - 5:00 PM
2SunClosed
HTH,
Jason