Блог

Главная - Вопросы по программированию - Нерабочие праздничные часы

Нерабочие праздничные часы

#sql #sql-server #unpivot

#sql #sql-сервер #нерабочий

Вопрос:

У меня есть таблица, которая отслеживает праздничные часы магазина:

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

Это очень упрощенная таблица. Существует около 30 столбцов, изначально состоящих из часов работы магазина по дате — это продолжается (DATE3, TIMES3, DATE4, TIMES4 и т. Д.).

Мне нужно отключить значения по вертикали, убедившись, что значения даты и времени находятся в одной и той же записи.

(ПРИМЕЧАНИЕ: как только я пойму, как правильно структурировать UNPIVOT выражение, я буду самостоятельно использовать динамический SQL для преобразования имен столбцов)

Желаемый результат:

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

Я пытался использовать UNPIVOT , но я застрял. Есть идеи?

ОБРАЗЕЦ ДАННЫХ:

 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' )

Код, который я пробовал:

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

Ответ №1:

Гордон на 100% прав ( 1).

Однако, если вы ищете динамический подход БЕЗ использования динамического SQL, рассмотрите следующее.

Пример

 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

ВОЗВРАТ

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

Функция с табличным значением, если она заинтересована

 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)
)

Комментарии:

1. Спасибо, Джон! Я получаю синтаксическую ошибку — это потому, что мы на SQL server 2012?

2. @DepthofField обновлен для версии XML по сравнению с подход JSON.

3. @DepthofField сначала примените ФУНКЦИЮ CREATE

4. О, я сразу же принял другой, поскольку это был первый опубликованный ответ! Оглядываясь назад, я понимаю, что ваш работает лучше для меня, поскольку он обрабатывает столбцы динамически и был обновлен для работы с 2012. Обновлено как таковое 🙂

5. @JohnCappelletti . , , Ваши версии, использующие XML и JSON, всегда очень умны.

Ответ №2:

Не используйте unpivot . Использовать 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 это нестандартный синтаксис, который выполняет ровно одну вещь. APPLY является ли SQL Server реализацией lateral join s. Это очень мощный join тип — его использование для отключения — хороший способ начать изучение синтаксиса.