Как развернуть строки в колонки запросом в 1С 8.3

В языке запросов 1С нет оператора PIVOT, поэтому «повернуть» таблицу (строки сделать колонками или наоборот) приходится вручную. Рабочих способов три: условное агрегирование через ВЫБОР КОГДА ... ТОГДА с СУММА, разворот через ОБЪЕДИНИТЬ ВСЕ и временные таблицы, а для динамического числа колонок — поворот средствами системы компоновки данных.

Выбор зависит от одного условия: знаете ли вы заранее, сколько колонок получится. Если набор фиксирован (12 месяцев, три склада) — подойдёт запрос. Если колонки заранее неизвестны (сколько в отчёт попадёт контрагентов — столько и столбцов) — только СКД или код на встроенном языке.

Ниже — конкретные конструкции запроса с проверенным синтаксисом 1С 8.3, разбор, когда какой метод оправдан, и ошибки, на которые легко наступить.

Поворот через условное агрегирование (ВЫБОР + СУММА)

Это основной приём, когда колонок известное количество. Идея простая: по каждой будущей колонке пишется своя сумма с условием. Строки группируются, а ВЫБОР КОГДА отбирает в каждый столбец только нужные значения.

Разложим продажи по месяцам — месяц уходит в колонки, номенклатура остаётся в строках:

ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(ВЫБОР КОГДА МЕСЯЦ(Продажи.Период) = 1
          ТОГДА Продажи.Количество ИНАЧЕ 0 КОНЕЦ) КАК Январь,
    СУММА(ВЫБОР КОГДА МЕСЯЦ(Продажи.Период) = 2
          ТОГДА Продажи.Количество ИНАЧЕ 0 КОНЕЦ) КАК Февраль,
    СУММА(ВЫБОР КОГДА МЕСЯЦ(Продажи.Период) = 3
          ТОГДА Продажи.Количество ИНАЧЕ 0 КОНЕЦ) КАК Март
ИЗ
    РегистрНакопления.Продажи.Обороты КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура

Что здесь важно:

• 🔹 ИНАЧЕ 0 обязательно. Без него в ячейку попадёт NULL, и последующие арифметические операции дадут NULL по всей строке.
• 🔹 Агрегатная функция (СУММА, реже МАКСИМУМ) нужна, потому что по каждой группе строк может прийти несколько записей — их надо свернуть в одно число.
• 🔹 Все поля вне агрегатов перечисляются в СГРУППИРОВАТЬ ПО.

Итоговый столбец «Всего» добавляется отдельным полем — просто суммой показателя без условия:

    СУММА(Продажи.Количество) КАК Всего

Оно встанет рядом с помесячными колонками и даст сумму по строке. Так же легко добавить разбивку по второму признаку — например, по складу: для каждого склада пишется свой блок условных сумм.

Главный плюс метода: вся работа идёт на стороне СУБД, результат приходит готовым. Главный минус: колонки прибиты гвоздями. Добавился апрель — правьте текст запроса. Когда условий становится больше десятка, текст разрастается — это сигнал перейти на СКД.

Разворот через объединения и временные таблицы

Обратная задача — сделать из колонок строки (это тоже транспонирование, только «вниз»). Такое нужно, когда несколько показателей одной записи надо вытянуть в отдельные строки: цену закупки, цену продажи и наценку по каждому товару.

Решается через ОБЪЕДИНИТЬ ВСЕ: каждый запрос в объединении достаёт одну колонку и подписывает её текстовой меткой.

ВЫБРАТЬ
    Цены.Номенклатура КАК Номенклатура,
    "Закупка" КАК Показатель,
    Цены.ЦенаЗакупки КАК Значение
ИЗ
    Справочник.Номенклатура.ЦеныПоставщиков КАК Цены

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Цены.Номенклатура,
    "Продажа",
    Цены.ЦенаПродажи
ИЗ
    Справочник.Номенклатура.ЦеныПоставщиков КАК Цены

Два правила объединения:

1. Количество и порядок полей во всех частях совпадают.
2. Типы полей в одной позиции совместимы. Если в первой части Значение — число, во второй там тоже должно быть число.

Используйте именно ОБЪЕДИНИТЬ ВСЕ, а не ОБЪЕДИНИТЬ. Простое ОБЪЕДИНИТЬ отсекает дубли строк, тратит на это ресурсы и может «съесть» нужные повторы.

Когда разворот — промежуточный шаг большого запроса, результат объединения кладут во временную таблицу через ПОМЕСТИТЬ:

ВЫБРАТЬ
    Данные.Номенклатура КАК Номенклатура,
    Данные.Показатель КАК Показатель,
    Данные.Значение КАК Значение
ПОМЕСТИТЬ ВТ_Развёрнутые
ИЗ
    (ВЫБРАТЬ ... ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ ...) КАК Данные

Дальше к ВТ_Развёрнутые можно соединять другие таблицы и строить итоговую выборку. Это чище и быстрее, чем тащить объединение во вложенный запрос несколько раз.

Если по развёрнутой таблице потом идёт соединение, добавьте к ней индекс — это ускорит соединение на больших объёмах:

ПОМЕСТИТЬ ВТ_Развёрнутые
...
ИНДЕКСИРОВАТЬ ПО
    Номенклатура

Индекс оправдан, когда строк в таблице много и по полю Номенклатура идёт СОЕДИНЕНИЕ. Для мелких таблиц на пару десятков строк он лишний.

Динамический поворот средствами СКД

Если число колонок заранее неизвестно, запросом задачу не решить — число столбцов в тексте запроса нельзя сделать переменным. Здесь работает только система компоновки данных.

В СКД колонки собираются из данных автоматически: сколько уникальных значений попадёт в группировку по колонкам — столько столбцов и построится.

Порядок настройки:

1. Напишите обычный линейный запрос — без разворота, строки как есть (номенклатура, месяц, количество).
2. На вкладке Ресурсы схемы компоновки отметьте поле-показатель (Количество) и функцию (Сумма).
3. На вкладке Настройки в структуре отчёта добавьте элемент Таблица.
4. В строки таблицы поставьте группировку по тому полю, что останется слева (Номенклатура).
5. В колонки таблицы поставьте группировку по тому полю, что развернётся вправо (Месяц).

Результат — настоящая сводная: месяцы встанут в шапку, номенклатура в бок, в пересечениях — суммы. Добавятся данные за новый месяц — появится новый столбец без правки схемы.

СКД — предпочтительный путь для отчётов, которые видит пользователь: он получает группировки, итоги, отбор и настройку внешнего вида «из коробки».

Транспонирование на встроенном языке (клиент или сервер)

Иногда данные уже лежат в ТаблицаЗначений, и надо честно поменять местами строки и колонки — то, что было в шапке, увести в первый столбец, и наоборот. Запросом это не делается, нужен код.

Разместите процедуру в общем модуле с флагом Сервер — обработка таблицы в памяти на сервере быстрее и не гоняет данные на клиент.

Функция ТранспонироватьТаблицу(Исходная) Экспорт
    Результат = Новый ТаблицаЗначений;
    Результат.Колонки.Добавить("Показатель");

    // колонка на каждую строку исходной таблицы
    Для Сч = 1 По Исходная.Количество() Цикл
        Результат.Колонки.Добавить("Строка" + Формат(Сч, "ЧГ=0"));
    КонецЦикла;

    // каждая исходная колонка становится строкой результата
    Для Каждого ИсхКолонка Из Исходная.Колонки Цикл
        НоваяСтрока = Результат.Добавить();
        НоваяСтрока.Показатель = ИсхКолонка.Имя;
        Для Сч = 1 По Исходная.Количество() Цикл
            ИмяКолонки = "Строка" + Формат(Сч, "ЧГ=0");
            НоваяСтрока[ИмяКолонки] = Исходная[Сч - 1][ИсхКолонка.Имя];
        КонецЦикла;
    КонецЦикла;

    Возврат Результат;
КонецФункции

Разбор ключевых мест:

• Число приводится к строке через Формат(Сч, "ЧГ=0") — без ЧГ=0 в имени колонки появился бы разделитель разрядов, и обращение по имени упало бы.
• Исходная[Сч - 1] — индекс строки нумеруется с нуля, поэтому из счётчика 1..Количество() вычитается единица.
• Обращение [ИмяКолонки] и [ИсхКолонка.Имя] идёт по имени — это работает для любого набора колонок.

Этот способ грузит всю таблицу в память, поэтому применяйте его только к небольшим наборам — десятки, максимум сотни строк. Для тысяч записей разворачивайте запросом или через СКД.

Если исходные данные приходят запросом, выгрузите их в таблицу методом Результат.Выгрузить() и передайте в функцию.

Какой метод выбрать под задачу

Коротко: число колонок известно — запрос; неизвестно — СКД; данные уже в таблице и нужен буквальный разворот — код.

По скорости: условное агрегирование и СКД работают на стороне СУБД и справляются с большими данными. Разворот через объединение дешевле, чем кажется, если он завёрнут во временную таблицу с индексом. Обработка ТаблицаЗначений на встроенном языке — самая медленная, её держат про запас для мелких таблиц.

По трудозатратам картина обратная. Быстрее всего правится условное агрегирование — но ровно до того момента, пока колонок немного. Как только список столбцов начинает меняться от запуска к запуску, запрос превращается в обузу, и выгоднее один раз настроить СКД. Код на встроенном языке пишется дольше всего и его сложнее поддерживать, поэтому к нему прибегают, когда данные уже в памяти и разворачивать их запросом поздно.

Отдельно о подготовке данных. Перед разворотом убедитесь, что показатель уже свёрнут до нужной детализации. Если в регистре по одной номенклатуре за месяц лежат десятки движений, а вы разворачиваете их без агрегата, в ячейку попадёт лишь одно значение вместо суммы. Поэтому в запросе всегда стоит СУММА или источник берётся из виртуальной таблицы оборотов, которая уже агрегирует данные.

Частые ошибки при транспонировании

Ошибки здесь однотипные и почти все — про NULL, группировку и границы цикла.

NULL вместо нуля в пустых ячейках. Забытое ИНАЧЕ 0 в ВЫБОР КОГДА. Пустое пересечение становится NULL, и любая сумма или сравнение по строке даёт NULL. Лечится либо ИНАЧЕ 0 в конструкции ВЫБОР, либо обёрткой ЕСТЬNULL(Поле, 0) на выходе.

Поле не попало в группировку. Если поле стоит в выборке вне агрегатной функции, но не указано в СГРУППИРОВАТЬ ПО, запрос не выполнится. Правило простое: всё, что не завёрнуто в СУММА/МАКСИМУМ/МИНИМУМ, перечисляется в группировке.

Несовпадение полей в объединении. Разное число колонок или несовместимые типы в одной позиции ОБЪЕДИНИТЬ ВСЕ. Проверьте, что во всех частях одинаковый порядок и тип: число под числом, ссылка под ссылкой.

Разделитель разрядов в имени колонки. При формировании имён колонок кодом — число приклеено к строке без Формат(Число, "ЧГ=0"). Имя Строка1 000 ломает обращение по индексу.

Показатель в группировке СКД вместо ресурсов. Поле суммы вынесли в группировку по строкам или колонкам — итоги не считаются. Показатель всегда в ресурсы.