UNPIVOT Holiday Hours

Question

I have a table, that keeps track of store holiday hours:

LOCATION_ID DATE1       TIMES1          DATE2       TIMES2
123456      2020-12-12  10:00AM-09:00PM 2020-12-19  10:00AM-09:00PM

This is a highly oversimplified table. There's about 30 columns horzontially consisting of store operating hours by date - It continues (DATE3, TIMES3, DATE4, TIMES4, etc).

I need to unpivot the values vertically, ensuring the date and time values are on the same record.

(NOTE: Once I figure out to structure the UNPIVOT expression properly, I will use Dynamic SQL on my own to pivot the column names)

Desired Outcome:

LOCATION_ID    DATE         TIME
123456         2020-12-12   10:00AM-09:00PM
123456         2020-12-19   10:00AM-09:00PM

I tried using UNPIVOT, but I'm stuck. Any ideas?

SAMPLE DATA:

CREATE TABLE #HOURS (LOCATION_ID int, DATE1 varchar(255), TIMES1 varchar(255), DATE2 
varchar(255), TIMES2 varchar(255));

INSERT INTO #HOURS VALUES ('123456', '2020-12-12', '10:00AM-09:00PM','2020-12-19','10:00AM-09:00PM' )

Code that I tried:

SELECT * 
FROM   (SELECT location_id, 
               [date1], 
               [times1], 
               [date2] 
        FROM   #hours) AS cp 
       UNPIVOT ( pivotvalues 
               FOR pivvalues IN ([Date1], 
                                 [date2], 
                                 [times1]) ) AS up1

user_1144035 · Answer

Don't use unpivot. Use apply:

select h.location_id, v.date, v.time
from #hours h cross apply
     (values (h.date1, h.times1), (h.date2, h.times2)
     ) v(date, time);

unpivot is non-standard syntax that does exactly one thing. APPLY is the SQL Server implementation of lateral joins. This is a very powerful join type -- using it for unpivoting is a good way to start learning the syntax.

user_1570000 · Answer

Gordon is 100% correct (+1).

However, if you are looking for a dynamic approach WITHOUT having to use Dynamic SQL, consider the following.

Example

Select Location_ID
      ,Date  = max(case when [Item] like 'DATE%' then Value end)
      ,Time  = max(case when [Item] like 'TIME%' then Value end)
 From (
        select A.Location_ID
              ,Grp = replace(replace([Item],'DATE',''),'TIMES','')
              ,B.*
         from #hours  A
         Cross Apply [dbo].[tvf-XML-Unpivot-Row]( (Select A.* for XML RAW) ) B
         Where [Item] not in ('LOCATION_ID')
      ) A
 Group By Location_ID,Grp

Returns

Location_ID   Date          Time
123456        2020-12-12    10:00AM-09:00PM
123456        2020-12-19    10:00AM-09:00PM

The Table-Valued Function if Interested

CREATE FUNCTION [dbo].[tvf-XML-UnPivot-Row](@XML xml)
Returns Table 
As
Return ( 
        Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
              ,Value = xAttr.value('.','varchar(max)')
         From  @XML.nodes('//@*') xNode(xAttr)
)

UNPIVOT Holiday Hours

2 Answers