Как объединить строки табличной части с разворотом категорий в колонки в запросе 1С

← На главную

При разработке отчетов и обработке данных в среде 1С:Предприятие программисты часто сталкиваются с задачей трансформации структуры данных. Типичный пример — необходимость свернуть (объединить) строки табличной части по ключевым полям (например, по контрагенту), при этом развернув детальные текстовые значения одного из измерений (например, категории объектов вроде M1, M2, N3) в отдельные колонки с подсчетом их количества. В теории баз данных эта операция называется транспонированием строк в колонки, или операцией Pivot.

В этой статье мы подробно разберем, как решить эту задачу оптимальным образом. Мы проанализируем ошибки в исходном подходе разработчика, разберем классическое решение через условное агрегирование в запросе, изучим динамический разворот в Системе компоновки данных (СКД) и рассмотрим программный метод работы с коллекцией ТаблицаЗначений на встроенном языке 1С.

Анализ проблемы и ошибок в исходном запросе

Рассмотрим ситуацию, с которой столкнулся автор темы. На входе имеется таблица со следующей структурой данных:

Контрагент (ссылка или строка);
Категория (строка: "M1", "M2", "N3" и т.д.).

В исходном виде для одного контрагента в таблице присутствует столько строк, сколько категорий за ним закреплено. Попытка автора решить задачу «в лоб» привела к созданию громоздкого запроса с четырьмя временными таблицами и левыми соединениями. Ключевая ошибка автора заключалась в следующей конструкции внутри секции выборки:

ВЫБОР КОГДА ВТ.Категория = "M1" Тогда Количество(ВТ.Категория) Конец КАК M1

Разберем, почему данный код работает некорректно и вызывает ошибки СУБД:

Неверное расположение агрегатной функции: В запросах 1С агрегатные функции (такие как КОЛИЧЕСТВО, СУММА, СРЕДНЕЕ) должны находиться снаружи условного оператора ВЫБОР, а не внутри него. Попытка поместить агрегат внутрь конструкции ТОГДА нарушает логику расчета и не позволяет СУБД корректно свернуть строки при группировке.
Детальное поле в выборке: Если в итоговом списке полей запроса присутствует поле Категория, то СУБД физически не сможет объединить строки по контрагенту. Для сервера базы данных записи с одинаковым контрагентом, но разными категориями — это абсолютно разные уникальные строки. Чтобы выполнить свертку, поле Категория должно быть полностью исключено из полей выборки и группировки финального запроса.
Избыточные соединения: Создание каскада временных таблиц (ВТ2, ВТ3, ВТ4) с последующим их соединением по ключевым полям создает колоссальную избыточную нагрузку на сервер СУБД и усложняет чтение и поддержку кода.

Решение №1: Оптимальный запрос с условным суммированием

Самым производительным и правильным решением на уровне СУБД является использование условной агрегации в рамках одного простого запроса с группировкой. Вместо каскада временных таблиц мы используем конструкцию СУММА(ВЫБОР...).

Суть метода заключается в том, что для каждой строки мы проверяем соответствие категории. Если строка относится к нужной категории (например, "M1"), мы возвращаем число 1, в противном случае — 0. Затем функция СУММА складывает эти единицы по всем сгруппированным строкам контрагента, возвращая точное количество записей.

Рассмотрим готовый пример такого запроса:


ВЫБРАТЬ
    т.Контрагент КАК Контрагент,
    СУММА(ВЫБОР КОГДА т.Категория = "M1" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК M1,
    СУММА(ВЫБОР КОГДА т.Категория = "M2" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК M2,
    СУММА(ВЫБОР КОГДА т.Категория = "M3" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК M3,
    СУММА(ВЫБОР КОГДА т.Категория = "N1" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК N1,
    СУММА(ВЫБОР КОГДА т.Категория = "N2" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК N2,
    СУММА(ВЫБОР КОГДА т.Категория = "N3" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК N3,
    СУММА(ВЫБОР КОГДА т.Категория = "O3" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК O3,
    СУММА(ВЫБОР КОГДА т.Категория = "O4" ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК O4,
    КОЛИЧЕСТВО(т.Категория) КАК КоличествоВсего
ИЗ
    &Таблица КАК т

СГРУППИРОВАТЬ ПО
    т.Contragent

Преимущества этого решения:

Высокая скорость работы: Запрос выполняется за один проход (Full Scan) по исходной таблице без тяжелых операций соединения JOIN и без создания временных таблиц в базе данных tempdb.
Отсутствие пустых значений (NULL): Благодаря конструкции ИНАЧЕ 0 в полях категорий всегда будут находиться числовые значения, что исключает ошибки при выводе данных в интерфейс или при выполнении арифметических операций на стороне встроенного языка.

Решение №2: Динамический разворот колонок через СКД

Описанный выше метод условной агрегации в запросе отлично работает, когда список категорий ("M1", "M2" и т.д.) строго зафиксирован и не меняется со временем. Однако, если завтра в системе появится новая категория (например, "O5" или "P1"), разработчику придется вручную переписывать запрос и добавлять новые колонки на форму.

Если конечной целью является вывод отчета пользователю, наилучшим решением будет использование Системы компоновки данных (СКД). СКД умеет автоматически выполнять операцию Pivot (разворот) динамически, анализируя состав данных прямо в момент формирования отчета.

Выполним настройку схемы компоновки данных по шагам:

Создадим максимально простой и «чистый» запрос в наборе данных СКД, который просто выбирает плоские записи:
```
ВЫБРАТЬ
    т.Контрагент КАК Контрагент,
    т.Категория КАК Категория,
    1 КАК Количество
ИЗ
    &Таблица КАК т
```
Перейдем на вкладку «Ресурсы» и добавим поле Количество. В качестве выражения укажем стандартную агрегатную функцию: Сумма(Количество).
Перейдем на вкладку «Настройки». Вместо привычной линейной структуры (Группировка строк) добавим элемент структуры с типом «Таблица».
В настройки созданной таблицы добавим измерения:
- В секцию «Строки» добавим поле Контрагент;
- В секцию «Колонки» добавим поле Категория.

При запуске такого отчета система компоновки данных сама определит уникальный список категорий, присутствующих в выборке, динамически сформирует под них колонки в итоговом табличном документе и заполнит пересечения строк и колонок просуммированными значениями ресурса Количество. Это наиболее гибкий, современный и масштабируемый подход к решению задачи.

Решение №3: Программный метод свертки Таблицы Значений

Иногда требуется произвести объединение строк непосредственно в коде модуля (например, при подготовке данных к записи в другую табличную часть или перед отправкой по API), когда использовать СКД нецелесообразно. В таких сценариях эффективнее использовать объект платформы ТаблицаЗначений и его встроенный метод Свернуть.

Проанализируем алгоритм программного разворота данных по шагам:

Выгрузим результат простого запроса (или непосредственно строки табличной части) в объект ТаблицаЗначений.
Добавим в таблицу значений новые колонки для каждой категории (M1, M2 и т.д.) с типом данных "Число".
Заполним созданные колонки: обойдем таблицу в цикле и, если значение в базовой колонке Категория совпадает с именем новой колонки, запишем туда единицу.
Применим метод свертки, указав группируемые и суммируемые поля.

Посмотрим на пример программной реализации этого алгоритма:


// Предположим, что ТаблицаДанных уже заполнена исходными записями
// Колонки: Контрагент (Ссылка), Категория (Строка)

// 1. Добавляем колонки для разворота категорий
КатегорииДляРазворота = Новый Массив;
КатегорииДляРазворота.Добавить("M1");
КатегорииДляРазворота.Добавить("M2");
КатегорииДляРазворота.Добавить("N3"); // и так далее для всех нужных категорий

Для Каждого ИмяКатегории Из КатегорииДляРазворота Цикл
    ТаблицаДанных.Колонки.Добавить(ИмяКатегории, Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 0)));
КонецЦикла;

ТаблицаДанных.Колонки.Добавить("КоличествоВсего", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 0)));

// 2. Заполняем новые колонки единицами для последующего суммирования
Для Каждого СтрокаТаблицы Из ТаблицаДанных Цикл
    СтрокаТаблицы.КоличествоВсего = 1;
    ИмяКолонкиКатегории = СокрЛП(СтрокаТаблицы.Категория);
    
    // Проверим, есть ли у нас колонка под данную категорию
    Если ТаблицаДанных.Колонки.Найти(ИмяКолонкиКатегории) <> Неопределено Тогда
        СтрокаТаблицы[ИмяКолонкиКатегории] = 1;
    КонецЕсли;
КонецЦикла;

// 3. Сворачиваем таблицу значений
// Первым параметром перечисляем колонки группировки (строки), 
// вторым параметром — суммируемые числовые колонки
КолонкиГруппировки = "Контрагент";
КолонкиСуммирования = "M1, M2, N3, КоличествоВсего"; // формируется динамически из массива КатегорииДляРазворота

ТаблицаДанных.Свернуть(КолонкиГруппировки, КолонкиСуммирования);

// Теперь ТаблицаДанных содержит уникальные строки по Контрагентам 
// с просуммированными показателями по каждой категории

Этот способ незаменим, когда логика обработки является чисто процедурной, а структура колонок фиксируется в коде динамически на основании метаданных или настроек пользователя.

Резюме и рекомендации

Мы рассмотрели три принципиально разных подхода к решению задачи объединения строк с разворотом категорий в 1С. Выбор конкретного метода зависит от архитектурного контекста задачи:

Используйте условное суммирование в запросе (Решение №1), если структура категорий стабильна, а результат запроса планируется использовать для дальнейшей программной обработки, проведения документов или выгрузки в файлы. Это самый быстрый способ с точки зрения СУБД.
Отдавайте предпочтение СКД (Решение №2) при построении любых пользовательских отчетов. Это сбережет ваше время, избавит от «жесткого кодирования» названий категорий в запросе и предоставит пользователю гибкие возможности группировки и оформления данных.
Применяйте метод Свернуть объекта ТаблицаЗначений (Решение №3), если трансформация данных происходит исключительно в оперативной памяти на клиенте или сервере без прямого обращения к СУБД.