Разработка и Конфигурация · 13 мин
Как транспонировать таблицу в запросе 1С
Как транспонировать таблицу в запросе 1С: конструкция ВЫБОР КОГДА, группировка, итоги, сводные таблицы через код. Примеры запросов и проверка результата.
Транспонирование — это поворот таблицы: значения строк становятся колонками. В языке запросов 1С нет оператора PIVOT, как в некоторых СУБД, поэтому задача решается двумя способами: агрегированием с конструкцией ВЫБОР КОГДА ... ТОГДА прямо в тексте запроса или программной обработкой результата через ТаблицаЗначений и Сводную таблицу. Выбор зависит от того, известны ли будущие колонки заранее.
Если набор значений, которые нужно превратить в колонки, фиксирован (например, 12 месяцев, четыре квартала, перечисление с известными элементами), запрос с ВЫБОР КОГДА — самый быстрый и прозрачный вариант: вся логика выполняется на сервере СУБД. Если колонки формируются динамически (список контрагентов, складов, номенклатуры), текст запроса придётся генерировать программно или транспонировать уже готовый результат в коде.
Ниже разобраны оба подхода с рабочими примерами, ограничениями и способами проверки результата. Примеры ориентированы на управляемые формы и типовой синтаксис языка запросов 1С:Предприятия 8.3, но подходят и для более ранних версий платформы 8.x.
Когда нужно транспонирование и как выбрать подход
Типовая ситуация: регистр накопления хранит продажи в разрезе «Номенклатура — Месяц — Количество», а отчёт должен показать номенклатуру в строках, а месяцы — в колонках. Исходная таблица «длинная», результат — «широкий». То же встречается при выводе остатков по складам, плановых показателей по периодам, свойств номенклатуры из регистра сведений «ЗначенияСвойств».
Критерии выбора метода простые. Если список будущих колонок известен на этапе разработки и не меняется от запуска к запуску — пишите статический запрос с ВЫБОР КОГДА. Если колонки зависят от данных (например, склады выбираются пользователем, а их число не фиксировано) — генерируйте текст запроса в цикле перед выполнением либо получайте «длинную» таблицу и поворачивайте её кодом.
Как выбрать действие
Транспонирование в запросе через ВЫБОР КОГДА
Базовый приём: для каждой будущей колонки пишется агрегатное выражение вида СУММА(ВЫБОР КОГДА <условие на значение> ТОГДА <числовое поле> ИНАЧЕ 0 КОНЕЦ), а строковое измерение выносится в СГРУППИРОВАТЬ ПО. Условие «отрезает» из общей суммы только те строки, которые относятся к данной колонке.
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР
КОГДА МЕСЯЦ(Продажи.Период) = 1
ТОГДА Продажи.Количество
ИНАЧЕ 0
КОНЕЦ) КАК Январь,
СУММА(ВЫБОР
КОГДА МЕСЯЦ(Продажи.Период) = 2
ТОГДА Продажи.Количество
ИНАЧЕ 0
КОНЕЦ) КАК Февраль
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоГода, &КонецГода, Месяц, ) КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура
Здесь виртуальная таблица оборотов с периодичностью «Месяц» даёт по одной строке на номенклатуру и месяц, а ВЫБОР КОГДА раскладывает эти строки по колонкам. Группировка схлопывает результат до одной строки на номенклатуру. Для нечисловых данных вместо СУММА используйте МАКСИМУМ или МИНИМУМ — они корректно работают со строками и датами, если в пределах одной ячейки оказывается не более одного значения.
Обратите внимание: ветка ИНАЧЕ обязательна. Без неё для неподходящих строк вернётся NULL, и поведение агрегата станет менее предсказуемым при смешивании типов. Для сумм ставьте ИНАЧЕ 0, для максимумов строк — ИНАЧЕ "" или осознанно оставляйте NULL, если пустые ячейки допустимы.
- 1Определите строковое измерение (что останется в строках) и список будущих колонок
- 2Напишите выборку исходных данных с полем-признаком колонки и полем-значением
- 3Для каждой колонки добавьте СУММА(ВЫБОР КОГДА признак = значение ТОГДА значение ИНАЧЕ 0 КОНЕЦ) с псевдонимом
- 4Добавьте СГРУППИРОВАТЬ ПО по строковому измерению
- 5Проверьте результат в консоли запросов на тестовых данных с известными итогами
Динамические колонки: генерация текста запроса
Когда набор колонок заранее неизвестен, текст запроса собирается в коде. Сначала отдельным запросом получается список уникальных значений будущих колонок (например, склады из отбора пользователя), затем в цикле к основному запросу добавляются выражения ВЫБОР КОГДА с подстановкой конкретных ссылок через параметры.
ТекстЗапроса = "ВЫБРАТЬ
| Т.Номенклатура КАК Номенклатура";
Номер = 0;
Для Каждого СтрокаСклад Из СписокСкладов Цикл
Номер = Номер + 1;
ТекстЗапроса = ТекстЗапроса + ",
| СУММА(ВЫБОР КОГДА Т.Склад = &Склад" + Номер + "
| ТОГДА Т.Количество ИНАЧЕ 0 КОНЕЦ) КАК Колонка" + Номер;
Запрос.УстановитьПараметр("Склад" + Номер, СтрокаСклад.Ссылка);
КонецЦикла;
ТекстЗапроса = ТекстЗапроса + "
|ИЗ ... СГРУППИРОВАТЬ ПО Т.Номенклатура";
Два важных правила. Во-первых, ссылки подставляйте только через параметры, а не конкатенацией строковых представлений — это защищает от ошибок преобразования типов и делает запрос безопасным. Во-вторых, соответствие «колонка → склад» сохраняйте отдельно (например, в соответствии), потому что псевдонимы вида Колонка1 сами по себе не говорят, какой склад за ними стоит.
Ограничение подхода — размер текста запроса и число полей результата. При сотнях колонок запрос становится тяжёлым для оптимизатора СУБД, а результат неудобен для вывода. Практический порог, после которого стоит переходить на сводную таблицу или СКД, — обычно несколько десятков динамических колонок, хотя точная граница зависит от объёма данных и СУБД.
Транспонирование результата через ТаблицаЗначений
Если запрос уже вернул «длинную» таблицу, повернуть её можно в коде без повторного обращения к базе. Алгоритм: создаётся новая ТаблицаЗначений, в неё добавляется колонка строкового измерения, затем по выборке уникальных значений добавляются колонки-показатели, после чего данные переносятся поиском строки и заполнением ячейки.
НоваяТаблица = Новый ТаблицаЗначений;
НоваяТаблица.Колонки.Добавить("Номенклатура");
СоответствиеСтрок = Новый Соответствие;
Для Каждого Строка Из ИсходнаяТаблица Цикл
Если НоваяТаблица.Колонки.Найти("К_" + Строка.КодКолонки) = Неопределено Тогда
НоваяТаблица.Колонки.Добавить("К_" + Строка.КодКолонки);
КонецЕсли;
СтрокаНазначения = СоответствиеСтрок[Строка.Номенклатура];
Если СтрокаНазначения = Неопределено Тогда
СтрокаНазначения = НоваяТаблица.Добавить();
СтрокаНазначения.Номенклатура = Строка.Номенклатура;
СоответствиеСтрок.Вставить(Строка.Номенклатура, СтрокаНазначения);
КонецЕсли;
СтрокаНазначения["К_" + Строка.КодКолонки] = Строка.Значение;
КонецЦикла;
Имя колонки в таблице значений не может начинаться с цифры и содержать пробелы, поэтому к динамическим именам добавляется префикс. Соответствие используется как индекс «номенклатура → строка», чтобы не искать строку методом Найти на каждой итерации — на больших таблицах это даёт заметный выигрыш по времени.
Этот способ удобен, когда данные уже получены для других целей или когда логика поворота сложнее, чем «сумма по условию» (например, в ячейку нужно поместить структуру из нескольких полей). Минус — вся работа выполняется на сервере 1С в памяти, поэтому для очень больших выборок запрос с ВЫБОР КОГДА предпочтительнее.
Сводная таблица и СКД как альтернатива
Для интерактивного анализа ручное транспонирование часто вообще не нужно. Сводная таблица (объект СводнаяТаблица в толстом клиенте) и схема компоновки данных с настройкой «таблица» умеют раскладывать измерения по строкам и колонкам автоматически: в СКД достаточно поместить одно измерение в группировку строк, другое — в группировку колонок, а показатель — в ресурсы.
Плюс СКД в том, что пользователь сам может менять состав колонок настройками отчёта без изменения кода, а платформа сама сформирует оптимальный запрос. Минус — результат СКД сложнее использовать программно как источник данных для дальнейшей обработки: выгрузка в таблицу значений требует отдельного вывода через процессор вывода в табличный документ или коллекцию значений.
Сравнение вариантов
Быстро, вся логика на СУБД, результат сразу пригоден для обработки. Ограничение: колонки должны быть известны или сгенерированы заранее
Подходит для динамических колонок по параметрам. Ограничение: сложнее поддержка, запрос не виден в консоли целиком
Гибкая логика ячеек, не требует повторного запроса. Ограничение: нагрузка на память сервера 1С при больших объёмах
Интерактивные колонки без кода, пользовательские настройки. Ограничение: неудобно как источник данных для программной обработки
Проверка результата и типичные ошибки
Проверять транспонирование удобнее всего контрольными суммами. Сложите исходный показатель по всей выборке (например, общую сумму продаж) и сравните с суммой всех колонок результата — значения должны совпасть. Второй контроль: выберите одну строку (одну номенклатуру) и сверьте каждую её ячейку с отдельным запросом по этой номенклатуре и соответствующему периоду.
Консоль запросов — основной инструмент отладки. Переносите в неё сгенерированный текст вместе с параметрами и выполняйте на реальных данных до встраивания в отчёт. Если запрос собирается программно, выведите итоговый текст в журнал регистрации или в сообщение на время отладки — большинство ошибок (пропущенная запятая, неверный псевдоним) видны сразу.
Как проверить результат
Равна сумме всех колонок результата
Расхождение — потеряны строки или условие ВЫБОР КОГДА не покрывает все значения
Равно числу уникальных значений строкового измерения
Строк больше — группировка не сработала или в неё попало лишнее поле
Содержат 0 или осознанно оставленный NULL
NULL там, где ожидался 0, — отсутствует ветка ИНАЧЕ
Каждая колонка соответствует своему значению из соответствия
Данные смещены относительно заголовков — нарушен порядок генерации
FAQ
Есть ли в языке запросов 1С оператор PIVOT?
Нет, встроенного оператора транспонирования в языке запросов 1С нет. Поворот реализуется агрегатными функциями с конструкцией ВЫБОР КОГДА либо программной обработкой результата. Для интерактивных отчётов аналогом PIVOT служит СКД с группировками по строкам и колонкам.
Как транспонировать строковые, а не числовые значения?
Используйте МАКСИМУМ(ВЫБОР КОГДА условие ТОГДА СтроковоеПоле ИНАЧЕ "" КОНЕЦ). Это корректно работает, если на пересечении строки и колонки находится не более одного значения. Если значений может быть несколько, сначала решите, какое из них показывать, или сворачивайте их в одну строку программно.
Что делать, если колонок получается больше сотни?
Запрос с сотнями выражений ВЫБОР КОГДА тяжело компилируется и неудобен в использовании. В такой ситуации переходите на СКД с таблицей, сводную таблицу или выгружайте данные «длинными» и стройте поворот на стороне потребителя — например, в Excel через сводную таблицу.
Можно ли транспонировать таблицу значений «на месте», без создания новой?
Нет, типового метода поворота у ТаблицаЗначений нет. Создаётся новая таблица с нужным набором колонок, и данные переносятся в цикле. Для ускорения используйте Соответствие как индекс строк вместо метода Найти.
Почему после группировки строки не схлопываются?
Чаще всего в список выбираемых полей попало поле, не входящее в группировку и не обёрнутое в агрегатную функцию, либо группировка идёт по более детальному полю, чем нужно (например, по номенклатуре и складу вместо только номенклатуры). Проверьте секцию СГРУППИРОВАТЬ ПО — в ней должно быть только строковое измерение.
Как вывести итог по каждой колонке после транспонирования?
В запросе добавьте секцию ИТОГИ СУММА(Колонка1), СУММА(Колонка2) ... ПО ОБЩИЕ — появится итоговая строка с суммами по всем колонкам. При программном транспонировании итоги считаются отдельным проходом по новой таблице или методом Итог() для каждой колонки.
Что важно запомнить
PIVOT в 1С заменяется связкой СУММА(ВЫБОР КОГДА ... ИНАЧЕ 0 КОНЕЦ) и группировки по строковому измерению. Динамические колонки генерируйте в тексте запроса через параметры или поворачивайте ТаблицаЗначений в коде. Для интерактивных отчётов используйте СКД. Результат всегда проверяйте контрольной суммой против исходных данных.