у меня так и было, но мне нужно сделать без этого ибо мне потом запрос этот джойнить

Перед выполнением запроса сиквелу нужно создать план. А для плана нужно знать откуда (таблицы) и что  (колонки) нужно брать. В рантайме он не умеет

А в итоге какая задача? Просто пивот довольно экзотический оператор, обычно он не нужен вообще

Когда один пивот, то считает всё нормально и выдаёт правильное количество записей 1389

Врубаю второй пивот и оно считает дату в новой записи

Для каждого месяца создаёт новую дублирующую запись, вместо того, чтобы всё посчитать в одной

а по отдельности пивоты считают нормально

А что потом с этим пивотом делается? Резалтсет в итоге куда-то наружу отдаётся?

В одной — это как? Две колонки за одну дату с разными агрегатами? Тогда надо колонки с разными именами делать (date_agg1, date_agg2) , то есть в динамическом наборе будет в 2 раза больше колонок, а в агрегатной функции можно что-нибудь с CASE состряпать. Но лично я всё ещё не понимаю для чего может понадобиться этот франкенштейн. Если же за одну дату засуммировать, то sum(isnull(EnergyValue,0) + isnull(другая_колонка,0)) for PaymentDate

Вот примерно так (работоспособность не проверял, но идея должна быть понятна):
SELECT *
FROM (
 SELECT CONVERT(nvarchar(8), PaymentDate, 112) + N'_EnergyValue' AS PaymentDate,
   EnergyValue AS Value
 FROM tbl
 UNION ALL
 SELECT CONVERT(nvarchar(8), PaymentDate, 112) + N'_OtherValue' AS PaymentDate,
   OtherValue AS Value
 FROM tbl
) AS t PIVOT (
 SUM(Value) FROM PaymentDate IN (
   '20190101_EnergyValue', '20190101_OtherValue',
   '20190102_EnergyValue', '20190102_OtherValue'
 )
) AS p;

Коллеги, добрый день, подскажите в каком направлении копать.
SQL 2012 SP4

Есть хранимка в которой один SELECT.  Он выбирает данные из других таблиц и в частности из ячейки с XML.

SELECT
   Status [Trade!1!Status]
  ,Parent [Trade!1!ParentID]
  ,isnull(Hierarchy.value('(/*/@IsVolumeFloating)[1]', 'int'), 0) [Trade!1!IsVolumeFloating]
  ,Hierarchy.value('(/*/@Index)[1]', 'int') [Trade!1!IndexID]
  ,Hierarchy.value('(/*/@Sequence)[1]', 'int') [Trade!1!Sequence]
....... и т.д. и вывод идёт тоже в XML ......
  for xml explicit
 
В какой-то момент процедура перестаёт выдавать некоторые ячейки. Например вместо
...... Status="1" IsVolumeFloating="0" IndexID="126" Sequence="4" ......
остаётся
...... Status="1" IsVolumeFloating="0" Sequence="4" ......

Вызываешь хранимку из студии - есть значение.  Из приложения - нет. Вызываю из PS invoke-sqlcmd - тоже нет.

Но главное, если перекопилировать процедуру через sp_recompile, или удалить её план, или FREEPROCCACHE, то недели две работает нормально.

То есть тут один pivot заменяет два мои?

Уверены, что проблема появляется-исчезает на одних и тех же данных?
Режим explicit  выбран сознательно?

Да

На разных.  
Про explicit. Писал запрос не я, думаю сознательно, судя по именованию полей в селекте, ну и там довольно здоровый XML в итоге получается, я только часть привёл.

Моя задача такова: Есть ID, в этих ID есть данные по датам в формате(дд-мм-гггг)  по ним нужно посчитать EnergyValue,LastValue,PrevValue.  Год с которого начинается отчёт берётся с переменной. В итоге должно быть минимум 36 столбцов( 12 месяцев - EnergyValue, 12 месяцев - LastValue,12 месяцев - PrevValue)

@Pluhha там, наверное, с NULL нужно разобраться

Подозреваю что с этим что-то, но как разные планы могут выдавать разные данные?  И всегда из приложения используется один план, а из студии другой (смотрел по execution_count планов).