Как использовать СВОДНУЮ таблицу, состоящую из значения Datetime

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я пытаюсь преобразовать строки в столбцы с помощью PIVOT, один из столбцов будет иметь значения даты и времени как NVARCHAR (MAX), но я получаю эту ошибку «Ошибка преобразования при преобразовании даты и / или времени из символьной строки».

Вот таблица, тип столбца для [Value] — NVARCHAR(MAX)

 Id | ColumnName   | Value 
01 | EmployeeName | Tommy
02 | Id           | 5A477EC0-F6A9-4BD4-9B69-D4C8253E8E75
03 | StartDate    | Jun 27 2015 10:25PM

Я пытаюсь преобразовать строки в столбцы и вставить результат в MyTable, который

 CREATE TABLE MyTable (EmployeeName NVARCHAR(50) NULL, 
                [Id] UNIQUEIDENTIFIER NULL, 
                [StartDate] datetime NULL)

INSERT INTO MyTable ([EmployeeName],[Id],[StartDate]) 
SELECT [EmployeeName],[Id],[StartDate]
FROM (
    SELECT Value, ColumnName
    FROM Employee ) x
PIVOT (
MAX(Value)
FOR ColumnName in ([EmployeeName],[Id],[StartDate])) p

Ожидаемый результат в MyTable будет выглядеть так

 Id                                   | EmployeeName | StartDate
5A477EC0-F6A9-4BD4-9B69-D4C8253E8E75 | Tommy        | Jun 27 2015 10:25PM

1. Anukool это странно, ваш запрос хорошо работает для меня. Может быть, есть другие строки, кроме тех, которые вы упомянули, которые не выполняют запрос?

2. Выполняется ли этот запрос на другом компьютере с другими региональными настройками? Если вам действительно нужно хранить datetimes в varchar, всегда используйте формат, не зависящий от региона. См. karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

3. @AnukoolGandhi . , , Ваш код, похоже, работает, когда я его пробую: dbfiddle.uk /. …

4. Я пропустил указание, что я пытаюсь ввести результат в другую таблицу, в которой есть столбец типа Datetime, я обновил его выше, извините за недоразумение

Ответ №1:

Я бы использовал условную агрегацию :

 insert into MyTable ([EmployeeName], [Id], [StartDate]) 
    select max(case when Columnname = 'EmployeeName' then value end) as EmployeeName,
           max(case when Columnname = 'Id' then value end) as Id,
           max(case when Columnname = 'StartDate' then value end) as StartDate
    from (select e.*, row_number() over (partition by Columnname order by id) as seq
          from Employee e
         ) e
    group by seq;

Ответ №2:

Я попытался воспроизвести вашу ситуацию, однако она работает отлично:

 SELECT [EmployeeName],[Id],[StartDate]
FROM (
    SELECT Value, ColumnName
    FROM @tbl ) x
PIVOT (
MAX(Value)
FOR ColumnName in ([EmployeeName],[Id],[StartDate])) p

И примеры данных:

 DECLARE @tbl table
(
ID INT,
Columnname VARCHAR(50),
[Value] NVARCHAR(max)
)

INSERT INTO @tbl
(
    ID,
    Columnname,
    Value
)
VALUES
  (01, 'EmployeeName', 'Tommy')
, (02, 'Id', '5A477EC0-F6A9-4BD4-9B69-D4C8253E8E75')
, (03, 'StartDate', 'Jun 27 2015 10:25PM')

ВЫВОД:

 EmployeeName                   Id                                    StartDate
Tommy           5A477EC0-F6A9-4BD4-9B69-D4C8253E8E75            Jun 27 2015 10:25PM

Обновить:
У вас возникла эта ошибка, когда вы вводите varchar такие значения, как 5A477EC0-F6A9-4BD4-9B69-D4C8253E8E75 , Tommy в поле StartDate типа datetime . Поэтому старайтесь избегать вставки значений не даты в поле StartDate .

1. Это была моя ошибка, есть еще один шаг в этом процессе, когда я пытаюсь поместить эти значения в другую таблицу с типом столбца DateTime, и именно там он выдает ошибку. Я обновлю приведенную выше формулировку проблемы

2. Спасибо, я считаю, что ошибка появляется, потому что я пытаюсь поместить значение nvarchar ’27 июня 2015 10:25 вечера’ в столбец Datetime, есть ли способ выбрать его в правильном формате, чтобы он был преобразован в date как часть инструкции insert?