Использование STUFF с датой

Question

Я пытаюсь использовать функциональность STUFF в SQL Server 2016, чтобы выбрать информацию DATE и вернуть ее в таблицу. Иногда есть несколько дат, чтобы вернуться. Я уже использовал STUFF для получения других нужных мне данных.

Email = STUFF((SELECT ', ' + [Value] 
  FROM EmailTable  
    WHERE ID = r.ID AND EmailType = 1 
      FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,'')

Это может вернуть несколько электронных писем, где это применимо, и работает нормально. Теперь проблема в том, что я хочу сделать то же самое, но с датой.

Date = STUFF((SELECT ', ' + DateValue 
  FROM DateTable  
    WHERE ID = r.ID
      FOR XML PATH(''),TYPE).value('(./text())[1]','DATE'),1,2,'')  

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

Ошибка последнего фрагмента кода в том, что он не будет работать из-за символа "+". Если я возьму запятую и добавлю плюс, я смогу вернуть DATE, но они будут заключены в теги XML.

Также он должен возвращать значение DATE, его нельзя преобразовать в NVARCHAR.

Я не уверен, выполнимо ли то, к чему я стремлюсь, но я подумал, что спрошу.

Если вам нужна дополнительная информация, пожалуйста, спросите.

Answer 1

Это слишком долго для комментария.

Ваш код не может возвращать несколько значений даты в одном столбце. SQL Server не предлагает типы массивов или их эквиваленты.

Какие FOR XML PATH делает это производить строку. Строка отформатирована как XML, но это все еще строка. Внутри строки вы можете определить типы атрибутов. Тем не менее, представление в виде строки.

Лучшее, что вы можете сделать, это сохранить дату в формате ГГГГ-ММ-ДД или ГГГГММДД, который легко конвертируется в дату в SQL Server (и почти в любом другом программном обеспечении).

Answer 2

То, что вы опубликовали, - это метод агрегирования строк, используемый в более старых версиях SQL Server. SQL Server 2017 предоставляет STRING_AGG для этого.

Этот метод генерирует значение XML из запроса, используя пустую строку в качестве имени элемента. .value('(./text())[1]','NVARCHAR(MAX)') в конце преобразует значение XML в текст. В заключение, STUFF, 1,2,'') удаляет ведущий разделитель

Поскольку вы хотите объединить строки и не хотите оставлять форматирование даты случайным, используйте FORMAT() для форматирования даты в нужную строку:

Email = STUFF((SELECT ', ' + FORMAT([DateValue],'yyyy-MM-dd')
                   FROM EmailTable  
                   WHERE ID = r.ID AND EmailType = 1 
                   FOR XML PATH(''),TYPE
               ).value('(./text())[1]','NVARCHAR(MAX)'),
              1,2,'')

Как это устроено

Выполнение самого внутреннего запроса с x в качестве имени элемента:

SELECT ', ' + format(DateValue,'yyyy-MM-dd')
FROM EmailTable
WHERE year=2019 and Day<5 and month=1
FOR XML PATH('x'),TYPE

Вернется:

<x>, 2019-01-01</x>
<x>, 2019-01-02</x>
<x>, 2019-01-03</x>
<x>, 2019-01-04</x>

Указав пустую строку в качестве элемента, мы получим:

, 2019-01-01, 2019-01-02, 2019-01-03, 2019-01-04

Это все еще значение XML, внутренним текстом которого является строка, которую мы хотим. Нам нужно извлечь это с .value('(./text())[1]','nvarchar(max)'):

select (  SELECT ', ' + format(DateValue,'yyyy-MM-dd')
          FROM EmailTable
          WHERE year=2019 and Day<5 and month=1
         FOR XML PATH(''),TYPE
       ).value('(./text())[1]','nvarchar(max)')

После этого нам нужно удалить ведущий разделитель, в этом случае два символа . T-SQL doesn't have aУДАЛИТЬstring function andSUBSTRINGneeds a length.STUFF` удаляет указанное количество символов перед добавлением новой строки, поэтому его можно использовать для удаления текста с начала.