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

← На главную

В практике разработчика 1С часто возникает задача преобразования вертикального набора данных в горизонтальный. Классический пример: есть список учеников, где каждый ученик указан в отдельной строке с привязкой к классу, а необходимо получить таблицу, в которой каждому классу соответствует одна строка, а имена учеников распределены по отдельным колонкам (Имя1, Имя2 и так далее). Эту операцию также называют транспонированием или Pivot.

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

Способ 1: Использование временных таблиц и функции АВТОНОМЕРЗАПИСИ

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

Разберем этот процесс по шагам:

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

Посмотрим на пример кода, реализующий этот алгоритм:


// Шаг 1: Нумеруем все записи во временной таблице
ВЫБРАТЬ
    Ученики.Класс КАК Класс,
    Ученики.Имя КАК Имя,
    АВТОНОМЕРЗАПИСИ() КАК НомерЗаписи
ПОМЕСТИТЬ ВТ_Нумерованная
ИЗ
    &ИсходныеДанные КАК Ученики
;

// Шаг 2: Находим минимальный номер для каждого класса
ВЫБРАТЬ
    ВТ_Нумерованная.Класс КАК Класс,
    МИНИМУМ(ВТ_Нумерованная.НомерЗаписи) КАК МинНомерЗаписи
ПОМЕСТИТЬ ВТ_Смещение
ИЗ
    ВТ_Нумерованная КАК ВТ_Нумерованная
СГРУППИРОВАТЬ ПО
    ВТ_Нумерованная.Класс
;

// Шаг 3 и 4: Распределяем по колонкам и сворачиваем
ВЫБРАТЬ
    Итог.Класс КАК Класс,
    МАКСИМУМ(Итог.Имя1) КАК Имя1,
    МАКСИМУМ(Итог.Имя2) КАК Имя2,
    МАКСИМУМ(Итог.Имя3) КАК Имя3,
    МАКСИМУМ(Итог.Имя4) КАК Имя4
ИЗ (
    ВЫБРАТЬ
        Нумерованная.Класс КАК Класс,
        ВЫБОР КОГДА (Нумерованная.НомерЗаписи - Смещение.МинНомерЗаписи) = 0 ТОГДА Нумерованная.Имя ИНАЧЕ "" КОНЕЦ КАК Имя1,
        ВЫБОР КОГДА (Нумерованная.НомерЗаписи - Смещение.МинНомерЗаписи) = 1 ТОГДА Нумерованная.Имя ИНАЧЕ "" КОНЕЦ КАК Имя2,
        ВЫБОР КОГДА (Нумерованная.НомерЗаписи - Смещение.МинНомерЗаписи) = 2 ТОГДА Нумерованная.Имя ИНАЧЕ "" КОНЕЦ КАК Имя3,
        ВЫБОР КОГДА (Нумерованная.НомерЗаписи - Смещение.МинНомерЗаписи) = 3 ТОГДА Нумерованная.Имя ИНАЧЕ "" КОНЕЦ КАК Имя4
    ИЗ
        ВТ_Нумерованная КАК Нумерованная
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Смещение КАК Смещение ПО Нумерованная.Класс = Смещение.Класс
) КАК Итог
СГРУППИРОВАТЬ ПО
    Итог.Класс

Важный нюанс: Этот метод стабильно работает, если данные во временную таблицу попадают уже упорядоченными, иначе АВТОНОМЕРЗАПИСИ() может присвоить номера в случайном порядке, что нарушит логику распределения.

Способ 2: Последовательные левые соединения (метод исключения)

Если мы заранее знаем, что количество элементов в группе невелико (например, не более 4-х), можно использовать метод последовательного выбора минимальных значений с исключением уже выбранных. Рассмотрим, как это работает:

Сначала находим минимальное имя (или минимальный ID записи) для каждого класса — это будет Имя1.
Чтобы найти Имя2, мы снова ищем минимальное значение для этого класса, но при условии, что оно больше, чем найденное Имя1.
Для Имя3 условие усложняется: значение должно быть минимальным среди тех, что больше Имя2.

Этот способ более громоздкий в написании («шашлык из запросов»), но он не требует использования АВТОНОМЕРЗАПИСИ() и может быть реализован в одном пакете без сложных расчетов индексов. Однако при увеличении количества колонок сложность запроса растет в геометрической прогрессии.

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

Если задача стоит не «получить временную таблицу строго в коде запроса», а «вывести данные пользователю или в ТЗ», то лучшим решением будет использование кросс-таблицы в СКД.

Проанализируем преимущества этого подхода:

Динамическое количество колонок. Если в одном классе 2 ученика, а в другом 10, СКД сама сформирует нужное количество столбцов. Вам не нужно заранее прописывать Имя1...Имя10.
Отсутствие сложных соединений. Запрос остается максимально простым (простая выборка двух полей), а вся магия разворота происходит в настройках компоновщика.
Программная выгрузка. Результат работы СКД можно легко выгрузить в ТаблицаЗначений программным способом.

Для реализации в настройках СКД достаточно добавить «Таблицу», в строки которой поместить поле Класс, а в колонки — поле Имя (или вычисляемое поле с номером по порядку).

Современное решение: Оконные функции (Платформа 8.3.24+)

Если вы работаете на актуальной версии платформы, задача решается значительно элегантнее с помощью оконной функции ПОРЯДКОВЫЙ_НОМЕР(). Выясним, как изменится логика:


ВЫБРАТЬ
    Ученики.Класс,
    Ученики.Имя,
    ПОРЯДКОВЫЙ_НОМЕР() С ГРУППИРОВКОЙ ПО Ученики.Класс УПОРЯДОЧИТЬ ПО Ученики.Имя КАК Индекс
ПОМЕСТИТЬ ВТ_СИндексами
ИЗ
    Справочник.Ученики КАК Ученики

После этого вам остается только применить финальную группировку с МАКСИМУМ(ВЫБОР...), как в первом способе. Это избавляет от необходимости создавать промежуточную таблицу ВТ_Смещение и выполнять лишние соединения, что существенно повышает производительность на больших объемах данных.

Что делать, если количество колонок неизвестно?

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

Сначала отдельным запросом получаем максимальное количество элементов в одной группе: ВЫБРАТЬ МАКСИМУМ(КолВо) ИЗ (ВЫБРАТЬ Класс, КОЛИЧЕСТВО(*) КАК КолВо ГРУППИРОВАТЬ ПО Класс).
В коде на языке 1С запускаем цикл от 1 до полученного максимума.
Внутри цикла формируем строку запроса, динамически добавляя поля МАКСИМУМ(ВЫБОР КОГДА Индекс = &Номер ТОГДА Имя ИНАЧЕ """" КОНЕЦ) КАК Имя&Номер.
Выполняем итоговый текст запроса.

Такой подход делает ваше решение по-настоящему универсальным и устойчивым к изменению исходных данных.