Как объединить данные из нескольких строк в одну с динамическим формированием колонок в 1С?

← На главную

В процессе работы с данными в 1С мы часто сталкиваемся с задачей, когда из нескольких детальных записей, объединенных общим ключевым полем, необходимо сформировать одну итоговую строку. При этом, данные из каждой детальной записи должны быть представлены в отдельных, динамически создаваемых колонках. Такая ситуация возникает, когда количество связанных записей заранее неизвестно и может варьироваться.

Представим, что у нас есть исходные данные, например, о подразделениях и организациях, привязанных к определенному номеру:

Исходные данные:


Номер | Подразделение | Организация
-----------------------------------
50    | Офис          | Главная
50    | Офис1         | Организация2
50    | Офис2         | Организация3

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

Требуемый результат:


Номер | Подразделение1 | Организация1 | Подразделение2 | Организация2 | Подразделение3 | Организация3
----------------------------------------------------------------------------------------------------
50    | Офис           | Главная      | Офис1          | Организация2 | Офис2          | Организация3

Давайте разберемся, как решить эту задачу в 1С.

Почему стандартным запросом это сложно сделать?

На первый взгляд, может показаться, что такую задачу можно решить с помощью обычного запроса 1С. Однако стандартные запросы 1С (которые по своей сути являются SQL-подобными) не имеют встроенных механизмов для "транспонирования" строк в столбцы, особенно когда количество столбцов заранее неизвестно и определяется данными. Мы можем использовать агрегатные функции, такие как ГРУППИРОВАТЬ ПО, и функции вроде СоединитьСтроки(), чтобы объединить значения из нескольких строк в одну строковую ячейку (например, "Офис, Офис1, Офис2"). Но это не создаст отдельные колонки для каждого значения.

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

Решение: Обход по группировкам и динамическое создание колонок в ТаблицеЗначений

Наиболее гибкий, мощный и распространенный подход к решению этой задачи в 1С — это использование программного обхода результата запроса по группировкам и динамическое создание колонок в объекте ТаблицаЗначений. Этот метод позволяет нам полностью контролировать структуру конечных данных.

Рассмотрим подробнее каждый шаг:

1. Подготовка исходных данных запросом

Начнем с формирования запроса, который выберет все необходимые детальные записи. Важно, чтобы поле, по которому мы будем объединять строки (в нашем примере это "Номер", а в приведенном на форуме коде — ГоловнойКонтрагент), присутствовало в выборке. Желательно также отсортировать данные внутри каждой группы, если важен порядок вывода в динамических колонках.


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| Контрагенты.ГоловнойКонтрагент КАК ГоловнойКонтрагент,
| Контрагенты.Наименование КАК Наименование,
| Контрагенты.Код КАК Код
|ИЗ
| Справочник.Контрагенты КАК Контрагенты
|ГДЕ
| Контрагенты.ГоловнойКонтрагент В(&ГоловнойКонтрагент)
|
|УПОРЯДОЧИТЬ ПО
| Контрагенты.ГоловнойКонтрагент, Контрагенты.Наименование
|
|ИТОГИ ПО
| ГоловнойКонтрагент";

Пояснение к запросу:

Мы выбираем ГоловнойКонтрагент (наше ключевое поле для группировки), а также Наименование и Код, которые будут динамически располагаться в колонках.
Условие ГДЕ Контрагенты.ГоловнойКонтрагент В(&ГоловнойКонтрагент) позволяет ограничить выборку конкретными головными контрагентами, что удобно для тестирования или получения данных по определенному набору.
Предложение УПОРЯДОЧИТЬ ПО Контрагенты.ГоловнойКонтрагент, Контрагенты.Наименование обеспечивает предсказуемый порядок следования детальных записей внутри каждой группы. Это важно для того, чтобы, например, "Офис1" всегда был в колонке "Подразделение1", а не "Подразделение2", если данные по конкретному головному контрагенту каждый раз приходят в разном порядке.
ИТОГИ ПО ГоловнойКонтрагент является ключевым моментом для нашего подхода. Он позволяет нам выполнить обход результата запроса по группам (в данном случае, по головному контрагенту), что существенно упрощает дальнейшую программную обработку. Без этого мы бы получили плоский список всех записей.

2. Создание ТаблицыЗначений для результата

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


// Создаем список значений для параметра запроса (пример)
Сп = Новый СписокЗначений;
Сп.Добавить(Справочники.Контрагенты.НайтиПоКоду("000016903"));
Сп.Добавить(Справочники.Контрагенты.НайтиПоКоду("000009426"));
Запрос.УстановитьПараметр("ГоловнойКонтрагент", Сп);

// Выполняем запрос
РезультатЗапроса = Запрос.Выполнить();

// Получаем выборку для обхода по группировкам.
// Метод Выбрать() с параметром ОбходРезультатаЗапроса.ПоГруппировкам
// возвращает выборку, где можно переходить от группы к группе.
ВыборкаГоловнойКонтрагент = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);

// Создаем ТаблицуЗначений, которая будет хранить наш результат
ТЗ = Новый ТаблицаЗначений;

// Добавляем фиксированную колонку для группирующего поля
// (например, ГоловнойКонтрагент, Номер и т.д.)
ТЗ.Колонки.Добавить("ГК");

Пояснение:

Мы создаем и заполняем СписокЗначений (Сп), который будет передан в качестве параметра &ГоловнойКонтрагент в наш запрос. Это позволяет нам гибко управлять тем, по каким головным контрагентам мы хотим получить данные.
Метод Запрос.Выполнить() возвращает объект РезультатЗапроса.
Ключевым моментом здесь является вызов РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам). Это дает нам специальную выборку, которую мы можем обходить по уровням группировок, определенным в запросе (в нашем случае, по ГоловнойКонтрагент).
Инициализируем новый объект ТаблицаЗначений (ТЗ), который станет хранилищем для нашего транспонированного результата.
Мы добавляем в ТЗ первую колонку с именем ГК. В нее мы будем записывать значения ГоловнойКонтрагент для каждой итоговой строки.

3. Обход результата запроса по группировкам и заполнение ТаблицыЗначений

Теперь приступим к основной логике: обходу результата запроса и динамическому добавлению колонок в нашу ТаблицуЗначений.


Пока ВыборкаГоловнойКонтрагент.Следующий() Цикл
    // Для каждой группы (ГоловногоКонтрагента) создаем новую строку в ТаблицеЗначений
    нов = ТЗ.Добавить();
    нов.ГК = ВыборкаГоловнойКонтрагент.ГоловнойКонтрагент;

    // Получаем детальную выборку для текущего ГоловногоКонтрагента
    ВыборкаДетальныеЗаписи = ВыборкаГоловнойКонтрагент.Выбрать();

    // Счетчик для формирования уникальных имен динамических колонок
    нКолонка = 0;

    Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
        // Динамически добавляем колонки, если их еще нет в ТаблицеЗначений.
        // Это делается один раз для всей таблицы.
        Если ТЗ.Колонки.Найти("К" + нКолонка) = Неопределено Тогда
            ТЗ.Колонки.Добавить("К" + нКолонка, Новый ОписаниеТипов("Строка")); // Колонка для Кода
        КонецЕсли;
        Если ТЗ.Колонки.Найти("Н" + нКолонка) = Неопределено Тогда
            ТЗ.Колонки.Добавить("Н" + нКолонка, Новый ОписаниеТипов("Строка")); // Колонка для Наименования
        КонецЕсли;

        // Заполняем значения в динамически созданные колонки текущей строки
        нов["К" + нКолонка] = ВыборкаДетальныеЗаписи.Код;
        нов["Н" + нКолонка] = ВыборкаДетальныеЗаписи.Наименование;

        // Увеличиваем счетчик для следующей пары значений
        нКолонка = нКолонка + 1;
    КонецЦикла;
КонецЦикла;

// В результате переменная ТЗ содержит транспонированные данные.
// Для проверки можно вывести ее, например:
// ПоказатьЗначение(, ТЗ);

Пояснение к обходу:

Внешний цикл Пока ВыборкаГоловнойКонтрагент.Следующий() Цикл перебирает каждую уникальную группу по ГоловнойКонтрагент. Для каждой такой группы будет создана новая строка в нашей итоговой ТаблицеЗначений.
Внутри внешнего цикла мы создаем новую строку в ТаблицеЗначений с помощью нов = ТЗ.Добавить() и сразу же заполняем ее фиксированную колонку ГК значением текущего головного контрагента: нов.ГК = ВыборкаГоловнойКонтрагент.ГоловнойКонтрагент;.
Далее мы получаем детальную выборку для текущего головного контрагента с помощью ВыборкаДетальныеЗаписи = ВыборкаГоловнойКонтрагент.Выбрать();. Эта выборка будет содержать все "Подразделения" и "Организации" (или "Коды" и "Наименования") для текущего головного контрагента.
Переменная нКолонка используется как счетчик для формирования уникальных имен новых колонок (например, "К0", "Н0", "К1", "Н1" и т.д.). Она обнуляется для каждой новой группы, так как нумерация колонок начинается заново для каждого головного контрагента.
Внутренний цикл Пока ВыборкаДетальныеЗаписи.Следующий() Цикл перебирает все детальные записи, относящиеся к текущему ГоловномуКонтрагенту.
Операторы Если ТЗ.Колонки.Найти("К" + нКолонка) = Неопределено Тогда ТЗ.Колонки.Добавить("К" + нКолонка, Новый ОписаниеТипов("Строка")); КонецЕсли; являются ключевым элементом динамического создания колонок. Они проверяют, существует ли уже колонка с текущим именем (например, "К0") в ТаблицеЗначений. Если колонка не найдена (Неопределено), то она добавляется с помощью метода ТаблицаЗначений.Колонки.Добавить(). Важно указывать ОписаниеТипов при добавлении колонок (например, Новый ОписаниеТипов("Строка")), чтобы обеспечить корректную работу с данными и избежать ошибок преобразования типов. Это гарантирует, что колонки создаются только один раз для всей таблицы, а не для каждой строки.
нов["К" + нКолонка] = ВыборкаДетальныеЗаписи.Код; и нов["Н" + нКолонка] = ВыборкаДетальныеЗаписи.Наименование; записывают значения из детальных записей в соответствующие динамически созданные колонки текущей строки ТаблицыЗначений.
нКолонка = нКолонка + 1; увеличивает счетчик, чтобы следующие детальные записи использовали новые пары колонок ("К1", "Н1" и так далее).

После выполнения этого кода в переменной ТЗ мы получим ТаблицуЗначений, где каждая строка будет соответствовать уникальному ГоловномуКонтрагенту, а связанные с ним Коды и Наименования будут расположены в динамически созданных колонках, как и требовалось. Это универсальное решение, которое адаптируется к любому количеству связанных записей.

Дополнительные подходы и рекомендации

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

Использование Системы Компоновки Данных (СКД) для динамических колонок

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

Кросс-таблицы (перекрестные отчеты):
В СКД мы можем настроить вывод данных в виде кросс-таблицы. Это позволяет нам выводить одно измерение в строках, а другое — в колонках. Если "Номер" является измерением строк, а "Подразделение" или "Организация" мы хотим видеть в динамических колонках, можно настроить их как измерения колонок. СКД автоматически сформирует нужное количество колонок на основе уникальных значений этих измерений. Однако это означает, что каждая колонка будет соответствовать конкретному уникальному "Подразделению" или "Организации" из всех данных (например, колонка "Офис" или "Офис1"), а не последовательному номеру повторения для одной группы (например, "Подразделение1", "Подразделение2").
Вычисляемые поля и ресурсы:
Мы можем использовать вычисляемые поля в СКД для формирования нужных значений, а затем размещать их в колонках. Для управления порядком динамических колонок (например, "Подразделение1", "Подразделение2") мы можем создать дополнительное вычисляемое поле, например, "Порядок", которое будет нумеровать колонки согласно нужной логике. Это поле затем можно сделать ресурсом с функцией "Максимум" (или другой агрегатной функцией) и использовать в настройках сортировки колонок.
Объединение полей в одну колонку (с постобработкой):
Если требуется вывести несколько значений в одну ячейку, например, через запятую, а затем программно разделить их на отдельные колонки, это можно сделать с помощью вычисляемого поля и функции СоединитьСтроки() в СКД. Однако для вывода в отдельные колонки в конечном итоге это все равно потребует дальнейшей программной постобработки полученного табличного документа или ТаблицыЗначений.
Динамическое добавление колонок в макет:
В более сложных случаях, когда состав колонок заранее не известен и не может быть полностью описан средствами СКД, мы можем использовать программное формирование макета отчета. Это очень похоже на подход с ТаблицейЗначений, но применяется уже на уровне макета СКД, где мы программно добавляем области и параметры в макет отчета, управляя их расположением и содержимым. Это дает максимальную гибкость, но требует более глубокого понимания работы с макетами и компоновщиком макета.

Программное транспонирование ТаблицыЗначений

Описанный выше подход с использованием ТаблицыЗначений можно сделать еще более универсальным и надежным.

Универсальная функция транспонирования:
Для более общего решения мы можем разработать универсальную функцию, которая будет принимать на вход любую ТаблицуЗначений и набор параметров (поле группировки, поля для транспонирования) и возвращать новую, транспонированную ТаблицуЗначений. Такая функция будет инкапсулировать логику обхода и динамического создания колонок, делая ее переиспользуемой в различных частях нашей конфигурации.
Управление типами колонок:
При динамическом добавлении колонок в ТаблицуЗначений критически важно указывать их тип. Использование Новый ОписаниеТипов("Строка"), Новый ОписаниеТипов("Число") и так далее обеспечивает корректную работу с данными, предотвращает ошибки преобразования типов и улучшает производительность, поскольку платформа сразу знает, какой тип данных ожидать. Это было добавлено в примере кода выше, и мы настоятельно рекомендуем это делать.
Использование временных таблиц в запросах:
Для предварительной подготовки данных перед программной обработкой мы можем использовать временные таблицы в запросе 1С. Это позволяет выполнить часть группировки, фильтрации и агрегации на уровне СУБД, что часто бывает быстрее и эффективнее, чем полная обработка всех данных на стороне 1С. Например, можно сначала выбрать все необходимые детальные записи во временную таблицу, а затем уже работать с ней, используя обход по группировкам.

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