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

← На главную

При разработке отчетов и обработок в платформах 1С часто возникает задача разворота вертикальной структуры данных («Объект — Свойство — Значение») в горизонтальную («Объект — Свойство1 — Свойство2 — Свойство3»). В классических СУБД для этого применяется оператор PIVOT, однако в языке запросов 1С такой встроенной конструкции нет.

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

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

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

Посмотрим на пример построения такого запроса:


ВЫБРАТЬ
    ВТ_Таблица.Объект КАК Объект,
    МАКСИМУМ(ВЫБОР
            КОГДА ВТ_Таблица.Свойство = &Свойство1
                ТОГДА ВТ_Таблица.ЗначениеСвойства
            ИНАЧЕ NULL
        КОНЕЦ) КАК Свойство1,
    МАКСИМУМ(ВЫБОР
            КОГДА ВТ_Таблица.Свойство = &Свойство2
                ТОГДА ВТ_Таблица.ЗначениеСвойства
            ИНАЧЕ NULL
        КОНЕЦ) КАК Свойство2,
    МАКСИМУМ(ВЫБОР
            КОГДА ВТ_Таблица.Свойство = &Свойство3
                ТОГДА ВТ_Таблица.ЗначениеСвойства
            ИНАЧЕ NULL
        КОНЕЦ) КАК Свойство3
ИЗ
    ВТ_Таблица КАК ВТ_Таблица
СГРУППИРОВАТЬ ПО
    ВТ_Таблица.Объект

Разберем механизм работы этого кода по шагам:

Для каждой строки таблицы оператор ВЫБОР проверяет, совпадает ли значение поля Свойство с требуемым параметром (например, &Свойство1).
Если условие выполняется, возвращается значение ЗначениеСвойства, иначе — NULL.
Инструкция СГРУППИРОВАТЬ ПО сворачивает все записи по полю Объект, а функция МАКСИМУМ выбирает непустое значение (так как NULL игнорируется при агрегации).

Этот подход не требует создания дополнительных индексов и не нагружает СУБД лишними операциями JOIN, что значительно повышает скорость выполнения запроса.

Способ 2. Разворот через объединение подзапросов (ОБЪЕДИНИТЬ ВСЕ)

Разберем еще один вариант решения, который использует аналогичную логику агрегирования, но разделяет фильтрацию по свойствам через операцию ОБЪЕДИНИТЬ ВСЕ во вложенном запросе.

Проанализируем пример кода:


ВЫБРАТЬ
    Данные.Объект КАК Объект,
    МАКСИМУМ(Данные.Свойство1) КАК Свойство1,
    МАКСИМУМ(Данные.Свойство2) КАК Свойство2,
    МАКСИМУМ(Данные.Свойство3) КАК Свойство3
ИЗ
    (ВЫБРАТЬ
        ВТ_Таблица.Объект КАК Объект,
        ВТ_Таблица.ЗначениеСвойства КАК Свойство1,
        NULL КАК Свойство2,
        NULL КАК Свойство3
    ИЗ
        ВТ_Таблица КАК ВТ_Таблица
    ГДЕ
        ВТ_Таблица.Свойство = &Свойство1

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

    ВЫБРАТЬ
        ВТ_Таблица.Объект,
        NULL,
        ВТ_Таблица.ЗначениеСвойства,
        NULL
    ИЗ
        ВТ_Таблица КАК ВТ_Таблица
    ГДЕ
        ВТ_Таблица.Свойство = &Свойство2

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

    ВЫБРАТЬ
        ВТ_Таблица.Объект,
        NULL,
        NULL,
        ВТ_Таблица.ЗначениеСвойства
    ИЗ
        ВТ_Таблица КАК ВТ_Таблица
    ГДЕ
        ВТ_Таблица.Свойство = &Свойство3) КАК Данные
СГРУППИРОВАТЬ ПО
    Данные.Объект

Данный способ также исключает тяжелые соединения, однако первый вариант с условным выражением ВЫБОР считается более компактным и удобным для поддержки.

Альтернативные способы транспонирования данных

В зависимости от архитектуры задачи и требований к динамическому составу колонок, можно использовать альтернативные инструменты платформы 1С:

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

Важные технические нюансы и ограничения

При использовании транспонирования в запросах следует учитывать следующие особенность работы платформы 1С:

Строки неограниченной длины: К полям типа «Строка неограниченной длины» нельзя применять агрегатные функции, в том числе МАКСИМУМ. Если значение свойства имеет такой тип, предварительно примените приведение типа: ВЫРАЗИТЬ(ЗначениеСвойства КАК Строка(200)).
Обработка нескольких значений одного свойства: Если у одного объекта содержится несколько записей по одному и тому же свойству, функция МАКСИМУМ вернет только одно наибольшее значение. Если требуется сохранить все значения, выборку необходимо дополнительно уникализировать или обрабатывать программно.