Так и делай названия столбцов EnergyValueMonth01, LastValueMonth01, LastValueMonth01, EnergyValueMonth01,.. etc Тогда тут и динамика не нужна

Чтобы название колонок с датами не дублировалось я сделал 3 разных столбца в которых генерируется эта же дата только +'_LastValue'  или +'_PrevValue'

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

Вот так сгенерировал для Pivot-a

А, не, я имел ввиду, что не конкретно на этом ID как в примере.  А так да, данные не меняются в этой ячейке с XMLем.   Вот просто запускаешь из студии - есть этот ID, дергаешь powershell'ом - нет. Всё повторяется пока не выкину план из кэша. После этого с теми же данными в обоих случае вывод одинаковый.

Это потому что я 3 пивота делать хотел, но они вместе работать не захотели и дубли начали выдавать

А что ВЫ подразумеваете под OtherValue?

@ElloVenera мне кажется ты учложняешь задачу. Проще сделать 36 агрегатных функций с Case'ами и всё. Да, не очень красиво, но пивотами несколько агрегатов не получится сделать.

Это я пытался придумать решение без исходных данных, второй пивот

Я бы с радостью сделал 36 кейсов, но у меня даты берутся с переменной и вдруг пользователь выберет два года вместо трёх)

хотя, я могу выражением скрыть их в ReportBuilder-e

Так, потихоньку-помаленьку выясняется что же нужно сделать. :)) Выкинть пивот и используй matrix в ssrs

Я новичок, не знаю что это

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

Создание матрицы (построитель отчетов) - SQL Server Reporting Services (SSRS) | Microsoft Docs
https://docs.microsoft.com/ru-ru/sql/reporting-services/report-design/create-a-matrix-report-builder-and-ssrs?view=sql-server-ver15

Благодарю!

Если какое-либо значение null - он не буде включено в формируемый xml без явного на то указания.

Не значение не null и не меняется.  В колонке Hierarchy примерно такой XML


<trd ProductType="2" Price="0" Volume="5.4" Index="126" Sequence="4">
 <kkd>
   <Period Period="0" Volume="0.2" PeriodUnit="1" PendingOperation="Persist" />
   <Period Period="1" Volume="0.2" PeriodUnit="1" PendingOperation="Persist" />
 ...  
 <Period Period="24" Volume="0" PeriodUnit="1" PendingOperation="Persist" />
 </kkd>
</trd>

Вот этот Index=126 не показывался.  После рекомпиляции стал. В SSMS всегда был виден.

Чую пахнет тикетом в Microsoft =(

Чудес не бывает.
Уберите for xml explicit и посмотрите что в исходных данных.